Special Expression for Calculating an Age/Anniversary:

It is easy to be off by a year when trying to calculate someone's age or an anniversary of some sort. An age/anniversary depends on whether or not the one for this year has occurred yet. You cannot just find the difference between the years. You must consider the month and day and compare them with today. So confusing!

Microsoft suggests a messy-looking but effective expression to handle this issue. It combines 3 functions: DateDiff, Format, and Int.

Age: DateDiff("yyyy", [Birthdate], Now())+ Int( Format(Now(), "mmdd") < Format( [Birthdate], "mmdd") )

Let's break this mess apart to see how it works.

Age will be the number of years from the DateDiff calculation plus 0, if we have passed the birth day already, or plus negative 1 (-1), if the birth day is still in the future.

Lessons Databases Appendix

Teachers: Request permission to use this site with your class
Copyright 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012