? simplest way to display age at last birthday anniversary ?

[R.S.Lynn]

I have a column containing dates of birth. I want a column displaying age
at last birthday anniversary. NOW()-B2 yields something like "6/23/1971"
meaning the person is 71 years, 6 months, and 23 days old. A column
containing such a result is OK, but I want a column that displays, for
example, simply "71"

What combination of functions will yield the result I want?

 

[Alan Cooper]

Just doing a custom format on the cell to YY should give the correct answer
(will just filter out 71 from your 6/23/1971).

 

[Sandy Mann]

at last birthday anniversary. NOW()-B2 yields something like "6/23/1971"

meaning the person is 71 years, 6 months, and 23 days old.

Coincidence which is not quite right. If somone is born on 1/1/99 on
1/1/200 they will be one year old but with those dates in A1 & B1, =B1-A1
produces "12/30/00" in your date system which you are taking as zero years,
12 months and 30 days old.

Use the DATEDIF function:

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

with the date of birth in A1

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


"

 

[R.S.Lynn]

I have tried both suggested solutions, and they both work, of course.
Thanks.

But now, I have lost a great deal of confidence in the Excel Help pulldown.
I entered "datedif" as a search term, and neither the online nor the
built-in help installed on my harddrive yielded any hits. I got some other
suggestions about "year(a1) - year(a2)" and so forth, but why no hit on
"datedif" ?

 

[R.S.Lynn]

Both suggested solutions worked for me, of course. Thank you all. But now
I have lost a great deal of confidence in the installed Help pulldown
system. I entered "datedif" as a search term, and got no reference to such
a function. I searched for date and time functions generally, and there was
no reference to "datedif" although obviously it exists because it worked for
me. Are there hidden functions that work but are not documented in the
built-in help? Even the MS Online help did not get a hit on "datedif" when
I entered that term. What am I missing here?

 

[JE McGimpsey]

See

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

I don't remember if MS has ever said why datedif() is not in Help, but
it's a deliberate decision. One could speculate that MS Legal is
involved in the decision somehow.

AFAIK, it's the only function that has no mention in Help.

 

[Opinicus]

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

Thanks for that link.

I don't remember if MS has ever said why datedif() is not
in Help, but
it's a deliberate decision. One could speculate that MS
Legal is
involved in the decision somehow.

I just discovered that the DATEDIF function won't return a
valid answer if the first date is earlier than 1 January
1900.

 

[Opinicus]

I just discovered that the DATEDIF function won't return a
valid answer if the first date is earlier than 1 January
1900.

Duh... I just remembered:

<quote>
About dates and date systems

Microsoft Excel stores dates as sequential numbers which are
called serial values. By default, January 1, 1900 is serial
number 1, and January 1, 2008 is serial number 39448 because
it is 39,448 days after January 1, 1900. Excel stores times
as decimal fractions because time is considered a portion of
a day.
</quote>

 

[JE McGimpsey]

Take a look here:

http://j-walk.com/ss/excel/files/xdate.htm

 

[Amedee Van Gasse]

In <[email protected]>, JE
McGimpsey told us an interesting story. My reply to this story is at
the bottom of this message.

See

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

I don't remember if MS has ever said why datedif() is not in Help,
but it's a deliberate decision. One could speculate that MS Legal is
involved in the decision somehow.

<conspiracy theory>
Let me guess... Lotus has a patent on the datedif() function.

</conspiracy theory>