General discussion


Excel Formula Continuous Services Date

By pvbridges ·
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.
Paul (

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by dryflies In reply to Excel Formula Continuous ...


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 (G. Next, I tried another approach, I took the sum of days and used the G5 formula WRT <day0> to format the output.

Collapse -

by pvbridges In reply to

This solved my problem. Thanks!

Collapse -

by pvbridges In reply to Excel Formula Continuous ...

This question was closed by the author

Related Discussions

Related Forums