Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula for Continuous Services Dates
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 ) +-------------------------------------------------------------------+ |Filename: Service Awards Final by Employee Temp.zip | |Download: http://www.excelforum.com/attachment.php?postid=3510 | +-------------------------------------------------------------------+ -- pvbridges ------------------------------------------------------------------------ pvbridges's Profile: http://www.excelforum.com/member.php...o&userid=24402 View this thread: http://www.excelforum.com/showthread...hreadid=380063 |
#2
|
|||
|
|||
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 -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=380063 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula including dates | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Formula to count number of dates in an array | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |