Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Range Mess | Charts and Charting in Excel | |||
To create formula to add 3 years and subtract 1 day from a date? | Excel Worksheet Functions | |||
I need the difference between two dates expressed as 4 years 3 mo. | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) |