Displaying a Contact’s age and countdown to the next Birthday
I got inspired by your last tip about displaying the days left before a task needs completion and tried to tweak it to create a countdown to a contact’s next birthday and also to create a column which displays the current age of that contact.
However, I can’t seem to come up with the correct formula.
Is it possible to create these columns for my contact list and if so, could you post the correct formula to use?
The formulas for calculating a contact’s age and the amount of days until his/her next birthday are indeed a bit more complex than one might think. After all, they are just simple date calculations, right?
Current Age
The formula for displaying the contact’s current age is:
DateDiff('yyyy',[Birthday],Date())-IIf(DateDiff('d',Date(),DateSerial(Year(Date()),Month([Birthday]),Day([Birthday])))>0,1,0)
This formula might look a bit complex at first and you might wonder why a more simple Fix((Date() - [Birthday]) / 365) doesn't do the trick. The answer is: leap years. As the division isn’t an exact method, it could increase the age for any contact before his or her actual birthday
For contacts born on February 29th, it will still add a year for non-leap yeas.
Days until next Birthday
The formula to display the amount of days left until a contact’s next birthday is:
IIf(DateDiff('d',Date(),DateSerial(Year(Date()),Month([Birthday]),Day([Birthday])))>0,DateDiff('d',Date(),DateSerial(Year(Date()),Month([Birthday]),Day([Birthday]))),DateDiff('d',Date(),DateSerial(DateAdd('y',1,Year(Date())),Month([Birthday]),Day([Birthday]))))
This formula looks quite repetitive and also nests quite deeply. The reason for this is that we must check if the contact has had his or her birthday already this year.
For contact’s born on February 29th, it will countdown the days to the Birthday on March 1st for non-leap years.
Adding the custom column
Adding the custom column goes in the same way as described in: Display days left before task needs completion.
Note however that you can not add any custom columns to the People and Business Card view. You can add custom columns to the Card view though and any list view such as the Phone List view.
Isn't adding custom columns just "Incredible"? ;-)
Tweaking the display
If you’d like to display the age with “years” behind it and the countdown until the next birthday with “days” behind it, you can add the following at the end of the formula (even after the closing parentheses):
& ' years'& ' days'
Sorting by age
As you can’t sort by any custom fields, you can’t directly sort by the Age field. However, you can add the Birthday field itself and sort your contacts by that field; the result will be the same.
