calculating the years
 

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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255

calculating the years
 

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"DKY" wrote in message
...

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


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255

calculating the years
 

See

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

--
Kind regards,

Niek Otten

"DKY" wrote in message
...

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


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255

calculating the years
 

Thanks guys, the datedif works great!


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255

calculating the years
 

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255

calculating the years
 

The answer you got is entirely correct.

What did you expect and why?

--
Kind regards,

Niek Otten

"DKY" wrote in message
...

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255

calculating the years
 

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



"DKY" wrote in message
...

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=498255