Figuring age from DOB not working

JTL9161

Well-known Member
Joined
Aug 29, 2012
Messages
595
Office Version
  1. 365
Platform
  1. Windows
I have this formula =INT((J1-R1)/365) in Q1

J1 has the formula =Today( )
R1 has date of birth (for example 1/1/1970)

When I put in the DOB for 1 person as 8/25/1967 I get the age as 57 (in cell Q1) which is correct as the person will not be 58 till August 25, 2025

But for a person with a DOB of 02/14/1968 I am also getting an age of 57 which should be 56 till 2/14/25 when they turn 57.
It appears that any person with a DOB in February the formula is not working. For someone who has their birthday today (02/05/1970) would mean they are turning 55 today but when I change their date to 02/06/1970 their age stays at 55 when it should be 54.

Thanks in advance for your help
James
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try
Excel Formula:
=INT((J1-R1)/365.25)
or
Excel Formula:
=DATEDIF(R1,J1,"y")
 
Upvote 0
It is because using "365" is not always an exact year, as it does not account for leap years (every fourth year has 366 days!).

Many people will improve that by using 365.25 instead of 365, which is much better, but not always guaranteed to work (depending on the years actually involved).
 
Upvote 0
For example, there is this rule:
"Years evenly divisible by 4 are leap years, with the exception of centurial years that are not evenly divisible by 400. Therefore, the years 1700, 1800, 1900 and 2100 are not leap years, but 1600, 2000, and 2400 are leap years."

And for example, in a period of 6 years, you could have 1 leap year or 2, depending on the years involved (or maybe even none, due to the rule above).

The DATEDIF function, as Fluff shows, should give you more accurate results.
See: DATEDIF function - Microsoft Support
 
Upvote 0
Thanks to all. I will try your suggestions.
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top