Formula to calculate someone's exact age

N

NavyPianoMan

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

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

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
 
D

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top