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.
 
Super, thanks!
 
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.
 
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),"")
 
Stupid, stupid, stupid... I need to use 365.25 not .4. What a waste of a day... it seems to work correctly now.
 
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
A Outlook can't remember outlook.com, Exchange password. Using Outlook 0
S Related messages show in main Outlook window vice new Advanced Find windows Using Outlook 1
H Force Outlook 2019 with GMail 2-Step to Require Login? Using Outlook 0
G Retaining Tabs in outlook body Using Outlook 2
V Setting up Outlook 2021 on new computer Using Outlook 2
G Add Map It button to Custom Contacts Form in Outlook Outlook VBA and Custom Forms 1
X Custom icon (not from Office 365) for a macro in Outlook Outlook VBA and Custom Forms 1
Victor_50 Problem - Google Workspace will stop "unsafe" access to Outlook end 2024 Using Outlook 3
C New pc, new outlook, is it possible to import auto-complete emailaddress Using Outlook 4
T Outlook 365 won't take new working password Using Outlook 0
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
P Can't add custom field to custom Outlook form, it always adds to the Folder instead Outlook VBA and Custom Forms 2
B Sync Outlook Public Folders to Contacts Using Outlook 2
D Delete Outlook emails from MS server Using Outlook 12
B Outlook tasks and PDF Using Outlook 4
D Outlook 2019 is no longer asking for password ... Using Outlook 5
Kika Melo How to mark as Junk any message not from Contacts (in Outlook.com) Using Outlook 3
L Outlook attachments from OneDrive as links Using Outlook 0
G Outlook 365 My iCloud Outlook doesn’t work after reinstalling Microsoft365 on Windows 10 PC – now I get error message on contacts and calendar Using Outlook 1
T How to Export & Import GMAIL Contacts into Outlook 2021 ? Using Outlook 4
M Synchronization and backup of Outlook from local to server. Using Outlook 8
T How to get an EVENT COLOR option in Outlook 2021 ? Using Outlook 0
K How can I delete an e-mail from Outlook Using Outlook 1
V Outlook Error The Attempted operation Failed. An Object Could Not be found Outlook VBA and Custom Forms 0
P Yahoo/IMAP folder rename by Outlook desktop 365 Using Outlook 0
A Outlook 2019 folder counter Using Outlook 0
A Relocate Search Bar in Outlook Using Outlook 2
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 10
L Opening People Outlook 2021 Using Outlook 2
e_a_g_l_e_p_i Outlook 2021 not letting me setup my Gmail using pop Using Outlook 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
P Can no longer sync Outlook with iPhone calendar after iPhone update to 17.1.1 Using Outlook 7
O Outlook - Switch from Exchange to IMAP Using Outlook 2
e_a_g_l_e_p_i Is it possible to have a reminder in Outlook 2021 for every 90 days Using Outlook 3
farrissf Outlook 2016 Optimizing Email Searches in Outlook 2016: Seeking Insights on Quick Search vs Advanced Search Features Using Outlook 0
C Advanced search terms for "Outlook Data File" Using Outlook 1
N Reply to Outlook messages by moving messages to a specific Outlook folder Outlook VBA and Custom Forms 1
O How to find out the domain and server settings that my Outlook is using? Using Outlook 2
A Outlook 365 (OutLook For Mac)Move "On My Computer" Folder Items From Old To New Mac Computer Using Outlook 3
H Integrating Alexa & Outlook Pro 2021 Using Outlook 2
Z Automatically adjust Outlook Reading Pane from bottom to right depending on portrait or landscape window Using Outlook 1
Rupert Dragwater Background colors not saving in Outlook 365 Using Outlook 15
petunia Outlook tasks module sunsetting? Exchange Server Administration 3
G Save emails as msg file from Outlook Web AddIn (Office JS) Outlook VBA and Custom Forms 0
D Outlook VBA forward the selected email to the original sender’s email ID (including the email used in TO, CC Field) from the email chain Outlook VBA and Custom Forms 2

Similar threads

Back
Top