We help IT Professionals succeed at work.

Age between two dates

Matt Pinkston
Matt Pinkston used Ask the Experts™
on
I have a spreadsheet where column b is the birthdate and I need to determine the age of the person relative to august 31 of 2011.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=2011-YEAR(B1)-IF(MONTH(B1)>8,-1,0)
Change the -IF to +IF
Or change the -1,0 to 1,0 in the if.
Either way should work
Matt PinkstonEnterprise Architect

Author

Commented:
it comes back with a werid date 1/12/1900

I was hoping for just how of the indivudal was as of 8/31/2011

Commented:
assuming you had DOB = 31-Aug-83 (A1) and compared against 31-Aug-11 (B1)

You get 28 years  do =(B1-A1)/365 and format the cell as number 0 dp

remeber both columns (A1&B1) need to be in date format

alternatively you can also do :

=(DATE(2011,8,31)-A1)/365
Matt PinkstonEnterprise Architect

Author

Commented:
8/31/2011 is just a know date for all it is not a column I need to know the age of a person as of 8/31/2011

Commented:
=(DATE(2011,8,31)-A1)/365  will get you the result you require as long as you format the cell as number 0 dp....

where A1 = the cell where DOB is held for all
Most Valuable Expert 2011
Top Expert 2011

Commented:
=DATEDIF(B2,date(2011,8,31),"y")
for example. Format as Number if you see a strange date.
My formula returns the correct number. If you get 1/12/1900, just format the field to number. It's being formatted as a date.

Doing a simple DATEDIF for year won't work, because it would return '12/31/2010' as 1 year old, when it should return 0.

Doing wshark's example won't work either, because some years have 366 days, meaning that ages close to 31 Aug would return incorrect results in many cases.

What you need to do is what I've proposed:
2011-YEAR(B1) will return the number of years. You then have to subtract one if the person was born after Aug. Just remember to format the field to number and it will work fine.
Most Valuable Expert 2011
Top Expert 2011

Commented:
>>"Doing a simple DATEDIF for year won't work, because it would return '12/31/2010' as 1 year old, when it should return 0."

did you test that? returns 0 for me.
My apologies. It does seem to work. I had the feeling that it didn't work before. I know it doesn't work for SQL and I was almost sure it didn't in excel. Maybe in some previous version?
Either way, your formula works properly, so I apologize.