calculating the years

[DKY]

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?

[Arvi Laanemets]

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Niek Otten]

See

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

--
Kind regards,

Niek Otten

"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[DKY]

Thanks guys, the datedif works great!

[DKY]

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

Please Login or Register  to view this content.

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?

--
Kind regards,

Niek Otten

"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>