how do i subtract one date from another and get an age in years

[Guest]

i am trying to subtract a date of birth from today's date and get an age in
years. Can anyone help me?

 

[Niek Otten]

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/datedif.htm#Age

 

[Guest]

i went there and tried it. It's not working. I get a #name error.
=datedif(now(),G6,y) is the formula i tried. G6 is a cell that has an
=average formula in it and the cell is formatted for a date. I am trying to
get an average age from a differing number of people. i tried putting the
now and the y in "" and i got an #num for the y. The "now" returned no
particular error, just said it wouldn't work. Any suggestions?

 

[Frank Kabel]

Hi
are you using an English Excel version with the coma as delimter?

 

[Niek Otten]

=datedif(now(),G6,"y") note the quotes

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

i guess i am, it's a united states english version and when i use formulas
for other things, i use the comma as the delimiter.

 

[Guest]

i tried that and instead of #name i got #num as the error. i also tried it
with the quotes around the now and the now(). That was worse. lol Any other
suggestions? All this has been helpful and i appreciate the help you are
giving.

 

[Frank Kabel]

Hi
Niek has spottet the error
missing apostrophes

 

[Frank Kabel]

Hi
what value is in G6?
Also what does the formula
=ISNUMBER(G6) return

 

[Guest]

=isnumber(g6) returns TRUE
The value in G6 is an average of the dates of birth of everyone that is in
the spreadsheets, jan thru oct.

That average is derived from monthly dates of birth averages of all the
patients done in each month.

And I have tried to put quotes around the y and all that did was changed the
error code from #name to #num.

I really appreciate the effort you are putting forth to help me with this.
I'm a novice at using Excel, but i'm willing to learn, lol. Again, Thanks.

What is the signficance of =isnumber(g6)?

 

[Dave Peterson]

Excel stores dates as numbers from some base number. For most windows users,
that base date is Dec 31, 1899.

So Jan 1, 1900 would really have value of 1.

How about reversing the arguments:

=datedif(g6,now(),"y")

If that doesn't work, if you put
=now()
in a cell by itself, do you get today's date followed by the time?

(Just wondering what language your copy of excel uses. Maybe =datedif() is
different, =now() and maybe "y" should be something else)

 

[Dave Peterson]

That first portion was in reply to why use: =isnumber().

 

[Guest]

That did it! I reversed the g6 and the now() and it works! thanks.

That first portion was in reply to why use: =isnumber().