CODEWITHSUNDEEP
excel
wootscootinboogie
wootscootinboogie

Reputation: 8695

Date subtracting quandary Excel 2007

Answer is being returned as a date, even if i put =int( in front of this. Any obvious reasons why?

=YEAR(TODAY()-YEAR(J2))

where J2 is 4/10/1939 and the answer given is 06/28/05. Why is this not simply outputting the person's age?

Upvotes: 0

Views: 95

Answers (4)

barry houdini
barry houdini

Reputation: 46361

Don't you have the parentheses in the wrong place? I suspect you want this formula

=YEAR(TODAY())-YEAR(J2)

To make sure you see the result as a number format the result cell as general

....but that doesn't give "age" as most people would understand it because it increments always on Jan 1st rather than on the birthday. To get accurate age in years try DATEDIF function

=DATEDIF(J2,TODAY(),"y")

For your example that will give 72 today but change to 73 on the birthday, 4/10/2012

Note: DATEDIF is not well documented in Excel and probably doesn't appear in your "function list"....but you can use it by simply typing it in a cell

Upvotes: 2

Dewey Williams
Dewey Williams

Reputation: 1

Your formula should be =YEAR(TODAY())-YEAR(J2). The cell the formula is in must be formatted General or Number for it to display the correct value. Note that this will not give the proper age if the person has not had a birthday in the current year.

This gives a more accurate age =INT((TODAY()-(B1))/365)

Upvotes: 0

Excellll
Excellll

Reputation: 5785

This should work:

=INT(YEARFRAC(A1,TODAY()))

If you're trying this in the same cell as before, you will have to reformat the cell as 'General'. That's just one of the pains of Excel autoformatting.

Upvotes: 0

Raystafarian
Raystafarian

Reputation: 3022

try =INT((TODAY()-J2)/365.25) as your formula to return the age in years

Using =year(today()-J2) would return the age plus the century (1900) so you could just append -1900 from it, but the above solution works more simply

Upvotes: 0

Related Questions