calculating the years

[DKY]

I'm trying to figure out how to get the years from a 'hire date' in
cell. I currently have a date in cell A5 that is the hire date and
want the spreadsheet to figure out how many years the person has bee
with the company. I'm going to keep this as an ongoing thing on th
spreadsheet so currently I have a date of 7/11/2002 in that cell and i
another cell I have this formula
=TODAY()-A5
It gives me the days and I want to convert that to years. I trie
dividing it by 365 which worked when I changed the date in A5 t
today's date in 2002 but when I changed it to today's date in 2000 i
gave me 6.1 years. I think leap year has something to do with that.
Any suggestions

 

[Arvi Laanemets]

Hi

=DATEDIF(HiringDate,TODAY(),"Y")

 

[Niek Otten]

See

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

 

[DKY]

Thanks guys, the datedif works great

 

[DKY]

I've just noticed a problem. I've got this

Code:
--------------------
=DATEDIF(A6,TODAY(),"Y")
--------------------

The value in cell A6 is 8/18/1995 and for some reason It keeps giving
me 10 for the years. I checked the output of TODAY() and it gives me
the following 02/20/06, which is today's date so I can't figure out why
this thing is giving me the number 10.

 

[Niek Otten]

The answer you got is entirely correct.

What did you expect and why?

 

[Arvi Laanemets]

Hi

You asked for difference in years. DATEDIF(StartDate,EndDate,"Y") returns
the difference in full years. I.e.
until 8/18/1996 the difference is 0 years
at 8/18/1996 until 8/18/1997 the difference is 1 year
at 8/18/1997 until 8/18/1998 the difference is 2 years
at 8/18/1998 until 8/18/1999 the difference is 3 years
at 8/18/1999 until 8/18/2000 the difference is 4 years
at 8/18/2000 until 8/18/2001 the difference is 5 years
at 8/18/2001 until 8/18/2002 the difference is 6 years
at 8/18/2002 until 8/18/2003 the difference is 7 years
at 8/18/2003 until 8/18/2004 the difference is 8 years
at 8/18/2004 until 8/18/2005 the difference is 9 years
at 8/18/2005 until 8/18/2006 the difference is 10 years

When you want to be more specific, then the number of months remaining from
full years can be calculated as
=DATEDIF(A6,TODAY(),"YM")
(but the formula can return wrong result for some date combinations -
because the length of month varies from 28 to 31)

Another possibility to calculate remaining months is
=12*INT(DATEDIF(A6,TODAY(),"YD")/365.25)
, where the average length of month (365.25/12 days) is used


Arvi Laanemets