Formula to calculate someone's exact age

[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?

 

[Niek Otten]

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?
|

 

[T. Valko]

Excel isn't rounding the result. You're subtracting the calander year
numbers 1983 from 2007. The months/days are ignored. To find the age using
full years try this:

=DATEDIF(birth_date,NOW(),"y")

See this for more info on DATEDIF:

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

 

[Guest]

It worked! Awesome! Thanks for the help--you made my day!
--NavyPianoMan

 

[Guest]

It worked! Awesome! Thanks for the help--you made my day!
--NavyPianoMan

 

[David Biddulph]

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").

 

[T. Valko]

You're welcome. Thanks for the feedback!