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.

• DateDiff("yyyy", [Birthdate], Now()) calculates the difference between the year of the birth date and the current year. This is all you would need if you KNEW that the birthday for this year had already passed.

• Format(Now(), "mmdd") returns the month and day for today's date.

• Format( [Birthdate], "mmdd")  returns the month and day for the birth date

• Format(Now(), "mmdd") < Format( [Birthdate], "mmdd") asks if the month and day for today is before the month and day in the birth date. So, if today is March 5, 2006 and the birth date is May 25, 1972, we are looking at the question "Is March 5 < May 25", which is True.

• Int(expression) The Int function returns the integer part of a number. In this case we are using the Int function to get a number out of a logical comparison.
  A weird feature of programming is that True and False have to actually be number values for a computer to work with them. A False expression evaluates to 0. A True expression evaluates to -1.  So when today's date is before the birthday, the Int part is -1 and the full expression for Age adds -1 to the DateDiff. TaDa! We have the correct age, as of today! As soon as we get to May 25, the Int part of the expression evaluates to 0 and the DateDiff result is the true age.

Lessons Databases Appendix

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

Last updated: 30 Apr 2012