Software

Ask something
Discussion Locked

Excel Formula Continuous Services Date

0 helpful
3 replies

by pvbridges

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

Hire Date Term. Date
Original Hire: 9/10/1973 through 12/8/1978=
5 years, 2 months, 28 days

Rehired: 9/22/1980 throuhg 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 (bridgesp@rtpatlanta.com)

3 replies

by dryflies

Paul,

What I did was make the H column a raw number and just subtracted the two date cells for that row. you can do that because Excell stores dates as the number of days since jan 1, 1900 (PC only, 1904 for mac) by summing those two raw numbers we get the total number of employment days. I subtracted that from the end date of employment and got a virtual start date. then I used your formula from G5 to format the output (G8). Next, I tried another approach, I took the sum of days and used the G5 formula WRT to format the output.

This discussion is closed to new replies.