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])

[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