Formula to calculate someone's exact age

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's exact
age based on their birthdate and today's date (formatted military-style as
dd-mmm-yy). In the example below, the above formula produces an incorrect
answer because it seems to round up.
Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83) produces
the number "24", which is correct when you go by the year "yy", but actually
incorrect because this person isn't actually 24 years old until 17-Sep-07.
Why does EXCEL seem to round up?
 
Look here:

http://www.cpearson.com/excel/datedif.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I tried using (for example) "=YEAR(D6)-YEAR(E6)" to determine someone's exact
| age based on their birthdate and today's date (formatted military-style as
| dd-mmm-yy). In the example below, the above formula produces an incorrect
| answer because it seems to round up.
| Subtracting the following two dates YEAR(28-Aug-07)-YEAR(17-Sep-83) produces
| the number "24", which is correct when you go by the year "yy", but actually
| incorrect because this person isn't actually 24 years old until 17-Sep-07.
| Why does EXCEL seem to round up?
|
 
YEAR(D6-E6) would be nearer.

YEAR produces an integer number, so when you did that on each of the dates
individually you threw away the month and day information. You'd need to do
the subtraction first, then convert from days to years. You might, however,
get small errors with leap years if you just use the YEAR function in that
way, so better to use =DATEDIF(E6,D6,"y").
 
Back
Top