Calculating the age of an Outlook contact doesn't work

Status
Not open for further replies.

Jezz

Member
Outlook version
Outlook 2019 64-bit
Email Account
Outlook.com (as MS Exchange)
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?!
 
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.
 
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!
 
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.
 
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:

Code:
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?
 
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.
 
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. :)
 
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.
 
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!
 
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).

Code:
IIf([Birthday]<>"None",Int(DateDiff("d",DateSerial(Year([Birthday]),Month([Birthday]),Day([Birthday])),DateSerial(Year(Now()),Month(Now()),Day(now())))/365.25),"")
 
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?
 
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:

Code:
 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?
 
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>)
 
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>
 
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?
 
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.
 
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!
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
C Calculating Week Number into a TextBox Outlook VBA and Custom Forms 0
D How to Run a Report Based on Age of Inbox Items Outlook VBA and Custom Forms 0
F Automatically calculate age from birthdate Outlook VBA and Custom Forms 2
J Outlook 2010 does not let me put any account Using Outlook.com accounts in Outlook 3
P 3 of 5 PST files don't install from d:\outlook but only from D:\ Using Outlook 7
HarvMan January Windows 10 preview update force installs new Outlook Using Outlook 1
L Outlook 2010 - new installation on Windows 11 - aplzod32.dll is not a valid Add-in Using Outlook 8
J Outlook troubleshooting/logging - option grayed out Using Outlook 2
B Arrows missing from Outlook emails vertical scrollbar Using Outlook 0
G Outlook 2021 (New) doesn't respect default browser Using Outlook 8
B Outlook or iPhone turning tabs into spaces in Outlook Notes Using Outlook 1
P newly installed Office 365 includes OLD Outlook Using Outlook 6
R Outlook ribbon menu default? Using Outlook 7
H Spam email in Gmail not visible in Outlook Using Outlook 3
J How to transfer Win 10 Outlook to new Windows 11 pc? Using Outlook 10
J Renegade spam URL line displayed in old local Outlook 365 email title Using Outlook 3
G Reduce whitespace in Outlook desktop Contact Cards display Using Outlook 3
C Outlook classic via 365 Using Outlook 2
Dr. Demento Analogous Outlook code to read info into an array (or collection or whatever) Outlook VBA and Custom Forms 7
S Repair Outlook Using Outlook 8
V Outlook Form ListBox is not editable Outlook VBA and Custom Forms 2
F Outlook's contacts Using Outlook 1
D Outlook 2003 stopped dead Using Outlook 2
G Cannot receive emails from gmail account in Outlook 365 Using Outlook 1
E "Cannot display the folder. MS Outlook cannot access the specified file location" Using Outlook 8
P Outlook 2016 Working Offline Using Outlook 2
Rupert Dragwater Cannot reestablish gmail (email address) account in Outlook 365 Using Outlook 11
O Outlook 365 synchronisieren Exchange Server Administration 1
kburrows Outlook Classic - JPG files are corrupted when opened or saved Using Outlook 3
F Sync Outlook Calendar Using Outlook 0
G Change default font size in sticky notes - Outlook Desktop 2021 Using Outlook 2
C VBA in "New Outlook?" Using Outlook 0
D New Outlook with Business Basic Plans Using Outlook 0
D Outlook 2021 not working with Outlook 2003 installed Using Outlook 5
D Outlook 2003 stopped working - get they dialog box asking for username & Password Using Outlook 2
T Outlook 2021 hangs in close on taskbar occasionally Using Outlook 1
M Duplicate removal feature in Outlook 2021 is faulty Using Outlook 2
D.Moore Outlook COM addins source folder Using Outlook 12
P Removing Outlook 365 Account from Send/Receive Using Outlook 3
kburrows Outlook Automatically Merging Contacts Using Outlook 2
A Outlook 2016 Outlook 2016 vs. New Outlook Using Outlook 4
D Outlook Desktop App Email Software Using Outlook 0
efire9207 VBA Outlook Contacts Outlook VBA and Custom Forms 6
M Outlook not logging in to server Using Outlook 0
J Outlook macro to run before email is being send Outlook VBA and Custom Forms 3
R Outlook 2021 change view Using Outlook 2
K Outlook font corrupted in some point sizes, resets on close/open Using Outlook 2
J Is the Windows Outlook Tasks module really going to be gone? Using Outlook 6
F Outlook 2010 and Hotmail Using Outlook 1
A Outlook 2021 needs 'enter' for people search Using Outlook 2

Similar threads

Back
Top