Calculating the age of an Outlook contact doesn't work

[Jezz]

A few years ago I added a custom field to my Outlook contacts in order to display their age based on their birthday. I used the formula from http://www.slipstick.com/outlook/contacts/calculate-the-age-of-contact/ and it worked fine. As far as I can remember, it always showed the correct age.

However I recently noticed that it sometimes shows an incorrect age, and I can't figure out why. Sometimes it shows the person's age as being one year older than they really are, and sometimes one year younger than they really are. I've double-checked the formula used (and even deleted the custom field and re-added it), but it makes no difference. I'm now using Outlook 2010 (I was using 2007 previously) so I'm wondering if something might have changed with 2010 that is affecting how the formula works? The formula is literally identical to the one given on the above webpage.

Let me give a couple of actual examples:

1) One of my contacts has a birthday of 4 January 1989. (In my Outlook this shows as 04/01/1989 - international date format). Her TRUE age right now is therefore 24. But the formula displays her age as 23. It showed her age as 23 before her birthday last week, and it still shows it as 23 until now. But here's the weird thing: if I change her birthday, in her contact file, to be 01/01/1989 (ie. go back by three days), then her age changes to 24! And it stays at 24 for any date prior to 01/01/1989. But if I set her birthday to 02/01/1989 (or any date thereafter), then it drops down to 23. So the cross-over date is 01/01/1989. Weird!

2) Another contact has a birthday of 1 May 1989 (01/05/1988). Her TRUE age right now is therefore 24. But the formula displays it as 25 - so with this contact, it's displaying one year ahead, not one year behind as with the previous example above! But I've also found that if I change her birthday to the 2nd of the month (02/05/1988) then the formula shows the correct age of 24. Weirder still, I can change her birthday to the first day of any month between Feb-Sep 1988, and it shows her age (wrongly) as 25. But if I select any date between Feb-Sep 1988 *other* than the first of the month, then it shows the correct age of 24. And once I get to 1 Oct 1988, it shows her correct age of 24 then too. But for the first day of those mentioned months, it always shows the wrong date. So it seems to be related to the first of the month.

Can anybody (perhaps Diane Poremsky herself) help me out here please?!

 

[Diane Poremsky]

Is the field updating when the form is opened? I have a custom form that adds the date to the subject field and it only updates when the form is opened and saved.

I'll check out your scenarios and see if i can repro.

 

[Jezz]

Hi Diane, I'm not using a custom form here. I've implemented the formula by opening the regular contact file (regular contact form I guess), then switching to the All Fields page, clicking the "New" button at the bottom, and adding the formula to a new field there. The field does update itself automatically as I change the birthday from one date to another to test it.

Thanks for helping!

 

[Diane Poremsky]

Thanks - that will be easier than using the form.

It looks like it's using the US date format. Does the long date show the correct date? I have the long date on the All Fields tab and also when i add the field to the view.

 

[Jezz]

Not 100% sure what you're referring to, but if I go to the All Fields tab, and then select "Personal fields" from the menu there, I can see a field called Birthday which shows the date as "04 January 1989", which is correct. Is that what you mean?

My regional settings (in the Windows Control Panel) are set to use the international date format (dd/MM/yyyy) as I don't live in the States. That's a good point about the formula using the US format - I hadn't considered that!

So I've just tried switching the Day and Month part of the formula around, and now it seems to be fixed and working correctly!!!

Now my formula looks like this:

IIf([Birthday]<>"None",DateDiff("yyyy",[Birthday],Date()) - IIf(DateDiff("d",CDate(Day([Birthday]) & "/" & Month([Birthday]) & "/" & Year(Date())),Date())<0,1,0),"")

Can you confirm this is correct?

Is there any way to redo this formula so that it doesn't care about the date format?

 

[Diane Poremsky]

Yes, "04 January 1989" is what I was looking for - i wanted to make sure Outlook saw the date correctly, and best way is using long date format with the month spelled out.

I'm going to check with some people on this.

 

[Jezz]

Super, thanks!

 

[Diane Poremsky]

Are you sure it worked correctly in 2007? I just tested it in Outlook 2007/Windows 7 and it behaves the same - the age varies with the date format.

 

[Jezz]

Actually that's quite possible - I may have been mistaken about that. I'm (mostly) sure it did work when I first added the formula, several years ago. That may even have been Outlook 2003. Then again, maybe it never did work correctly and I just didn't notice it. I'm 95% sure it did work back then - I'm pretty sure I remember checking and seeing that it was accurate (I have dozens of contacts with birthdays and I'm sure they always had the right age) - but maybe not.

 

[Diane Poremsky]

I just repro'd it in Outlook 2003 on WinXP - so it looks like a limitation of the formula. (This was one that Sue wrote years ago.)




It's not using Date Serial, it's using the date as displayed in the shortdate format. Switching those two fields, as you did, will fix it. Thanks for bringing it to my attention and I will make note on the webpage. When I get a chance I'll convert it to use the serial date, which will ignore the display format.

 

[Jezz]

Awesome. I'll stick to the fixed formula then. Whenever you do convert it to the serial date, do you think you could post an update to this forum thread so I get notified?! Thanks again for helping me with this!

 

[Diane Poremsky]

Here is a version i came up with that seems to work ok - it might be off a day due to leap year (as in, the age might not change at midnight on the day of the birthday), if my logic is correct. (Date calculations can be confusing).

IIf([Birthday]<>"None",Int(DateDiff("d",DateSerial(Year([Birthday]),Month([Birthday]),Day([Birthday])),DateSerial(Year(Now()),Month(Now()),Day(now())))/365.25),"")

 

[Diane Poremsky]

Stupid, stupid, stupid... I need to use 365.25 not .4. What a waste of a day... it seems to work correctly now.

 

[Jezz]

Thanks again! So.... is it always perfectly correct now? Or will it still be out by a day or two on leap years? I'd prefer to use a formula that is always 100% correct on any given day - so that even when it's just one day before the person's birthday, it shows their true age. Will this formula do that now?

 

[Jezz]

BTW I just noticed your newer formula at To calculate the age of an Outlook contact - Slipstick Systems which you've modified to take account of babies under 1 year old: "To show the age of babies as 0 instead of -1, the formula compares the birthyear with the current year and enters a 0 if they match."

However, in my testing just now, I found that it's not necessary to make the formula compare the birthyear with the current year in this way, and enter 0 if they match. I found that the formula will give a correct age of 0 for babies as it stands here:

 IIf([Birthday]<>"None",Int(DateDiff("d",DateSerial(Year([Birthday]),Month([Birthday]),Day([Birthday])),DateSerial(Year(Now()),Month(Now()),Day(now())))/365.25),"")

I believe this is due to the "Int" variable which always rounds down. A baby who is less than one year old (has an age in days that is under 365) will have their "DateDiff/365.25" equal to "0.xxx", and this will be rounded down to just 0. Right?

 

[Diane Poremsky]

Int always rounds down (that's why i used it) but in my tests, it always resulted in -1 for the babies.... unless I was doing something stupid again (which is entirely possible... ). Sigh... yours is identical to mine a couple of posts above... and they are giving me 0's today. <sigh> Have I said yet how much i hate date calculations.

With the .25, it's perfect (I know why i stupidly entered .4... and I still can't believe i did it. <g>)

 

[Diane Poremsky]

I was thinking about it wrong again... we're not doing times, only dates. So mine changes at midnight too. I really, really hate working with date & time... (I'm Marty in Back to the Future - Doc tells me I'm not thinking 4-dimensional.)

In a test with Jan 10 birthdays from 1940 to now with 3 fields on the view - Sue's, mine, and one without the Int - all had the same age. Every 4th year had the exact age on the Int-less version, no decimals - and it doesn't change as time passes. And now I realize where I was thinking wrong (again!)... since we only check dates, not time, it will change at midnight.

Years ago I actually wrote a very helpful article about time calculations in Excel... I need to find it and re-read it. <g>

 

[Jezz]

Okay I'm a bit confused again - are you now saying the formula is still not quite right and needs further work? I'm not sure what you're implying about the time. Surely the time is irrelevant, as we're only dealing with a birth date and not a birth time? If the age switches over at midnight on their birthday, isn't that what we'd want?

 

[Forum Admin]

I'm 99.9% confident the final formula will be accurate all the time - I wasn't thinking it through correctly on the day I stupidly tried to use .4. (Still can't believe i did that.)

We're only working with dates whole dates, not time, and it will be correct.

The .01 doubt is how it will work for leap babies, but it should work the same as Sue's code.

 

[Jezz]

I agree, I think it's all good now. I couldn't find any faults with it when I tested it with different dates. And I don't have any leap babies in my contacts list so I'm not too worried about that anyhow!

Thanks again for troubleshooting this with me!