how to calculate the age of an employee

[Guest]

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

 

[Guest]

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

 

[SteveW]

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

 

[MartinW]

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

 

[SteveW]

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

Neat

 

[Roger Govier]

Hi Martin

That's very neat!!

 

[MartinW]

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

 

[Roger Govier]

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.

 

[SteveW]

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

 

[MartinW]

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

 

[Roger Govier]

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


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

 

[Guest]

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]

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

 

[Guest]

perfect
thanks so much

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