Formula for Continuous Services Dates

[pvbridges]

I'm trying to help our HR Department develop a spreadsheet to calculate
continuous service dates for employees, using Excel 2000.

_Hire_Date________________Term._Date__
(Employee) 9/10/1973 12/8/1978: 5 years, 2
months, 28 days
Rehired: 9/22/1980 12/31/2005: 25 years, 3
months, 9 days


Here is the formula I'm using to calculate =DATEDIF(E152,F152,"y") & "
years, " &DATEDIF(E152,F152,"ym") & " months, "
&DATEDIF(E152,F152,"md") & " days".

My problem is when an employee leaves and then comes back as in the
above example. I'm not able to get the two to sum, to give me a total
continuous service date. Any help would be most appreciated. An
example spreasheet is attached.
Thanks,
Paul ([email protected])


+-------------------------------------------------------------------+
|Filename: Service Awards Final by Employee Temp.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3510 |
+-------------------------------------------------------------------+

 

[olasa]

Put this formula in G6 (in you example):
=--TEXT(SUMPRODUCT(F4:F5-E4:E5),"YY")&" years,
"&--TEXT(SUMPRODUCT(F4:F5-E4:E5),"MM")&" months,
"&--TEXT(SUMPRODUCT(F4:F5-E4:E5),"DD")&" days"

HTH
Ola Sandström