Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Range Mess Karen Charts and Charting in Excel 18 January 14th 06 03:34 PM
To create formula to add 3 years and subtract 1 day from a date? rostroncarlyle Excel Worksheet Functions 2 December 9th 05 12:21 AM
I need the difference between two dates expressed as 4 years 3 mo. Dean Excel Discussion (Misc queries) 2 December 1st 05 06:11 AM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! RICHARD Excel Discussion (Misc queries) 0 March 1st 05 02:53 PM


All times are GMT +1. The time now is 03:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"