Time calculation

rsoper · May 21st, 2002, 08:02 AM

Name Hire Date TODAY YEARS
Arnett 09/12/1996 05/21/2002

I have a spreadsheet contianing employees. I would like to be able to automatically calculate years of employement based on "hire date" (Fixed) and "today" (Variable - using today function, in expression builder).

I would like the answer to be formated in one of the following YEARS or MONTHS or DAYS or a combination of the three.

Thanks for your help.

Mark W. · May 21st, 2002, 08:10 AM

=DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")& " months "&DATEDIF(A2,TODAY(),"YD")&" days"

where cell A2 contains your hire date.

sann0638 · May 21st, 2002, 08:33 AM

if you do TODAY-HIREDATE it will give you length of service in days.

rsoper · May 21st, 2002, 08:41 AM

It appears that the day calculation has something wrong

Example1
Date hire: 4/18/2001
Today: 5/21/2002

Given Answer: 1 year 1 month 33 days
Correct Answer: 1 year 2 months 3 days

Example2
Date hire: 9/12/1996
Today:5/21/2002

Given Answer: 5 years 8 months 251 days
Correct Answer: 4 years 9 months 6 days

Thanks again

Mark W. · May 21st, 2002, 08:46 AM

You're right!! I made a typo on the 3rd argument of the last DATEDIF. Instead, use...

=DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")& " months "&DATEDIF(A2,TODAY(),"MD")&" days"

If you have XL2000 you might want to have a look at the Help topic for "DATEDIF worksheet function" for an explanation of the 3rd argument.

[ This Message was edited by: Mark W. on 2002-05-21 07:49 ]

May 21st, 2002, 08:33 AM	#3
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	if you do TODAY-HIREDATE it will give you length of service in days. __________________ Regards, Mike.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 08:02 AM	#1
rsoper New Member Join Date: Mar 2002 Location: Robert Soper Posts: 23	Name Hire Date TODAY YEARS Arnett 09/12/1996 05/21/2002 I have a spreadsheet contianing employees. I would like to be able to automatically calculate years of employement based on "hire date" (Fixed) and "today" (Variable - using today function, in expression builder). I would like the answer to be formated in one of the following YEARS or MONTHS or DAYS or a combination of the three. Thanks for your help.

May 21st, 2002, 08:10 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")& " months "&DATEDIF(A2,TODAY(),"YD")&" days" where cell A2 contains your hire date.

May 21st, 2002, 08:41 AM	#4
rsoper New Member Join Date: Mar 2002 Location: Robert Soper Posts: 23	It appears that the day calculation has something wrong Example1 Date hire: 4/18/2001 Today: 5/21/2002 Given Answer: 1 year 1 month 33 days Correct Answer: 1 year 2 months 3 days Example2 Date hire: 9/12/1996 Today:5/21/2002 Given Answer: 5 years 8 months 251 days Correct Answer: 4 years 9 months 6 days Thanks again

May 21st, 2002, 08:46 AM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	You're right!! I made a typo on the 3rd argument of the last DATEDIF. Instead, use... =DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")& " months "&DATEDIF(A2,TODAY(),"MD")&" days" If you have XL2000 you might want to have a look at the Help topic for "DATEDIF worksheet function" for an explanation of the 3rd argument. [ This Message was edited by: Mark W. on 2002-05-21 07:49 ]