Jan's Working with Databases:

Calculate an Age or Anniversary

Home > Jan's CompLit 101 > Working with Databases > Appendix

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.

Home > Jan's CompLit 101 > Working with Databases > Appendix

Last updated: September 17, 2012