hi,

I have a list of employees with their date of births - for example 1/1/1988.

I need to have a column that works out the age with todays date.

Is this possible?

Thanks

Suzie

See Chip Pearson's website for instructions on using the DATEDIF function to
calculate age:
http://www.cpearson.com/excel/datedif.htm

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Grd" wrote:

hi,

I have a list of employees with their date of births - for example 1/1/1988.

I need to have a column that works out the age with todays date.

Is this possible?

Thanks

Suzie

If name is in A2, Put birthdate in B2

C2 = b2-TODAY()

Problem is that this will give you an age in Years , ie 43.21

If you want it in Years, Months, Days there is a function DATEDIF

So add the following, this will Years, Months and Days in D, E and F
D2 = DATEDIF($B2,TODAY(),"y")
E2 = DATEDIF($B2,TODAY(),"ym")
F2 = DATEDIF($B2,TODAY(),"md")

Steve

On Mon, 18 Sep 2006 15:39:01 +0100, Grd
wrote:

hi,

I have a list of employees with their date of births - for example
1/1/1988.

I need to have a column that works out the age with todays date.

Is this possible?

Thanks

Suzie

Hi Suzie,

Another way is with your birthdate in A1, then in B1 put
=TODAY()-A1

And Format B1 as custom
yy"y " mm"m " dd"d"

HTH
Martin

First try i got a date as the result
But hey, the formatting made sense of the numbers

Neat

On Tue, 19 Sep 2006 12:51:28 +0100, MartinW wrote:

Hi Suzie,

Another way is with your birthdate in A1, then in B1 put
=TODAY()-A1

And Format B1 as custom
yy"y " mm"m " dd"d"

HTH
Martin

Hi Martin

That's very neat!!

--
Regards

Roger Govier


"MartinW" wrote in message
...
Hi Suzie,

Another way is with your birthdate in A1, then in B1 put
=TODAY()-A1

And Format B1 as custom
yy"y " mm"m " dd"d"

HTH
Martin

Unfortunately not as neat as I thought!

I just tried it on a question in another group which was comparing
the dates 14/02/1980 to 28/02/1985 which should return
5y 0m 14d but this method returns 5y 1m 14d

Think it needs a bit more investigation.

Regards
Martin

Hi Martin

I had just been carrying out the same task myself, and realised the
"flaw".
I also tried in with dates of 16/09/06 and 19/09/06 and it gives the
"appearance" of 0y 01m 03d so it is adding an extra month to the result.

I will also continue to "play" as it looks such a neat way of doing
things.

--
Regards

Roger Govier


"MartinW" wrote in message
...
Unfortunately not as neat as I thought!

I just tried it on a question in another group which was comparing
the dates 14/02/1980 to 28/02/1985 which should return
5y 0m 14d but this method returns 5y 1m 14d

Think it needs a bit more investigation.

Regards
Martin

if you change your format to yyyy"y..."
it shows the full 19.. format for the year

In raw form today() is 38979 (19/09/2006)
1/1/1950 is 18264

Today()-date = 20715 (17/09/1956)

But actually it's 56y 9m and 18days

thnk it's hitting leap days in the years it is using

My brain is going need a cup of tea before I post any more

Steve



On Tue, 19 Sep 2006 13:40:49 +0100, MartinW wrote:

Unfortunately not as neat as I thought!

I just tried it on a question in another group which was comparing
the dates 14/02/1980 to 28/02/1985 which should return
5y 0m 14d but this method returns 5y 1m 14d

Think it needs a bit more investigation.

Regards
Martin

Hi Roger,

Changing the formula to =(TODAY()-A1)-31 does seem to get
it fairly close, I don't think it would ever be more than a day out
although it does give some strange results like 4y 12m 1d.

I'm thinking it has something to do with the known bug that
exists with the 1900 date system that I have read about on these
groups before but can't quite bring to mind right now.

Sounds like a question for Biff to me.

Regards
Martin

Hi Steve

No it isn't 9 months and 18 days, as we haven't passed the end of
September yet.
It seems to be out by a whole month each time.
Like you, a cup of tea is required - and come to think of it, I haven't
eaten any lunch yet!!!
Be back later.

--
Regards

Roger Govier


"SteveW" wrote in message
...
if you change your format to yyyy"y..."
it shows the full 19.. format for the year

In raw form today() is 38979 (19/09/2006)
1/1/1950 is 18264

Today()-date = 20715 (17/09/1956)

But actually it's 56y 9m and 18days

thnk it's hitting leap days in the years it is using

My brain is going need a cup of tea before I post any more

Steve



On Tue, 19 Sep 2006 13:40:49 +0100, MartinW
wrote:

Unfortunately not as neat as I thought!

I just tried it on a question in another group which was comparing
the dates 14/02/1980 to 28/02/1985 which should return
5y 0m 14d but this method returns 5y 1m 14d

Think it needs a bit more investigation.

Regards
Martin

Here's the basic issue with simply subtracting the dates.....
When formatted as any kind of date, Excel interprets the difference as a
date serial number and displays the value of that date.

Example:
Using
A1: 02/01/1957 and
A2: 02/01/2006
(which is obviously 49 years)

02/01/2006 - 02/01/1957 = 17,897 days
Excel interprets that as date serial number for 12/30/1948

Using the custom format of yy"y " mm"m " dd"d", you get
the YEAR of that date: 1948
the MONTH of that date: 12
and the DAY of that date: 30

However, using DATEDIF and the fomula from Chip Pearson's site:
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " &
DATEDIF(A1,A2,"md") & " days"

That difference calculates to:
49 years, 0 months, 0 days

***********
Regards,
Ron

XL2002, WinXP


"MartinW" wrote:

Hi Suzie,

Another way is with your birthdate in A1, then in B1 put
=TODAY()-A1

And Format B1 as custom
yy"y " mm"m " dd"d"

HTH
Martin

OK

Made a bit more sense out of it. It's no bug just normal maths.
Usual counting starts at 0, but when you are using month formatting
it starts at 1.

33 when shown in date format refers to 02/02/1900 so in yy mm dd
format will show as 00y 02m 02d yet in actual time it is really
00y 01m 02d.

As I said before the minus 31 in the formula should be close enough
for most practical purposes, so long as your not betting on the result ;-)

Regards
Martin

perfect
thanks so much

suzie

"Ron Coderre" wrote:

See Chip Pearson's website for instructions on using the DATEDIF function to
calculate age:
http://www.cpearson.com/excel/datedif.htm

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Grd" wrote:

hi,

I have a list of employees with their date of births - for example 1/1/1988.

I need to have a column that works out the age with todays date.

Is this possible?

Thanks

Suzie