MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Length of Service Calculations


Posted by Kevin Dodwell on December 18, 2001 1:40 AM

Can you help me construct a formula to calculate the length of service of an employee. The only data I have is their start date, and in some cases their leaving date (though not in the case of exisitng staff).

I need excel to take into account leap years and if possible show the result in years/days/months/days

Thank you


Posted by Paul on December 18, 2001 4:14 AM

If the start date is in B3 this will show the time worked in Y,M,D =DATEDIF(B3,NOW(),"y")&"y,"&DATEDIF(B3,NOW(),"ym")&"m,"&DATEDIF(B3,NOW(),"md")&"d"

This will show how long till the leaving date, just replace the leaving date with the date leave the"" =DATEDIF(TODAY(),"leaving date ","y")&"y,"&DATEDIF(TODAY(),"leaving date ","ym")&"m,"&DATEDIF(TODAY(),"leaving date ","md")&"d"