# How much long service bonus to pay? [Homework]

Posted on December 8th, 2017 in Formula Challenges - 22 comments

‘Tis Friday and it is too hot in my home office to stand and type a longish post. So, let’s keep this skirtish (short and pretty).

How would you calculate long service bonus?

Let’s say you are HR manager at BigLargeInc. and you are looking at Julia’s service details. You have her employment start date, current date, her leave without pay details, as shown below.

You need to calculate how many days of continuous service Julia has (ie total service – duration on leave without pay). How would you write the formula?

Assume cell references as shown in the picture above.

Bonus question? Think Julia’s troubles are nothing? Then come up with formulas to tell how many years and months of long service Julia had. Assume each year has 365.25 days and 12 months in it.

For the above data, you should get below answers

• Continuous service days: 4,870
• Continuous service in years and months: 13 years and 4 months

 5 conditional formatting top tips – Excel basics Merry Christmas and Happy New Year 2018 [Holiday Card]
 Written by Chandoo Tags: date and time, Excel for HR, homework, HR, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 22 Responses to “How much long service bonus to pay? [Homework]”

1. FirstTimePoster says:

Days:
=(C4-C3)-(C8-C7)

Years and Months
=INT(((C4-C3)-(C8-C7))/365.25) & " Years " & ((MOD(((C4-C3)-(C8-C7)),365.25)/365.25)*12) & " Months"

Continuous Service Days-I:
=DATEDIF(D4,D5,"D")-DATEDIF(D10,D11,"D")

Continuous Service Days-II:
=DATEDIF(D4,D5,"Y")& " years "&DATEDIF(D4,D5,"YM")-DATEDIF(D10,D11,"YM")&" months"

2. Toichat says:

In C10, Days = "=DATEDIF(C2,C3,"d")-DATEDIF(C6,C7,"d")"
In C11, Years = "=TRUNC(C10/365.25)"
In C12, Months = "=TRUNC(MOD(C10,365.25)/(365.25/12))"

• Toichat says:

Just noticed that I set my sheet up slightly differently to the post. C10 should read "=DATEDIF(C3,C4,"d")-DATEDIF(C7,C8,"d")"

3. Michael (Micky) Avidan says:

@FirstTimePoster,
To my opinion - the Months calculation part can be shorten to:
=(MOD(((C4-C3)-(C8-C7))/365.25,1)*12)
Micky

• FrankT says:

FirstTimePoster: 13 years and 4 months
Michael: 13 years and 4.00000000000001 months

4. Michael (Micky) Avidan says:

and with some arithmetic manipulation:
=MOD((C4-C3-C8+C7)/365.25,1)*12
Micky

5. Prem Singh says:

I think this function works well.
=DATEDIF(B1,B2,"d")-DATEDIF(B4,B5,"d")

6. GraH says:

=DATEDIF(C2,C3,"d")-DATEDIF(C7,C8,"d")
or =C7-C2+C3-C8
or =ROWS(INDIRECT(C2&":"&C3,TRUE))-ROWS(INDIRECT(C7&":"&C8,TRUE))
=DATEDIF(C2,C3,"y")-DATEDIF(C7,C8,"y")& " years and "&DATEDIF(C2,C3,"ym")-DATEDIF(C7,C8,"ym")&" months"
or
=INT(YEARFRAC(C2,C3,1)-YEARFRAC(C7,C8,1))&" Years and "&ROUND(MOD(YEARFRAC(C2,C3,1)-YEARFRAC(C7,C8,1),1)*12,0)&" Months"

7. FrankT says:

B12=C4-C3-(C8-C7)
B13=DATEDIF(1,B12,"Y") & " years and " & DATEDIF(1,B12,"YM") & " months"

8. Jules says:

To acquire the service days in B12 I used [ =DAYS(C4,C3)-DAYS(C8,C7) ]

Then to convert the answer in B12 into years, months and days I used -
[ =DATEDIF(0,B12,"y")&" years "&DATEDIF(0,B12,"ym")&" months "&DATEDIF(0,B12,"md")&" days" ]

9. Amit Jalan says:

Continuous Service Days
=((C4-C3)-(C8-C7))

Continous Service Year & Month
=DATEDIF(C3,C4,"Y")-DATEDIF(C7,C8,"Y")&" Years "&TEXT(MOD(((C4-C3)-(C8-C7))/365.25,1)*12,"#")&" Months"

10. anil says:

pl rephrase the question-as "total service – duration on leave without pay" should not be called continuos service-as I understand she worked continuosly for 1157 days and ,after unpaid leave, for 3713 days-pl clarify what is required

11. John O says:

Continuous Days: =(C4-C3)-(C8-C7)

I agree with Anil above..."continuous days" means something different than the definition given. That, and I distrust most HR departments. 🙂

In Years and Months: This will be a break from the DATEDIF function I see above. Nothing wrong with DATEDIF, I just did things differently:
=QUOTIENT(B12,365.25)&" years and "&QUOTIENT(MOD(B12,365.25),365.25/12)&" months"

12. Ola says:

Days: =C7-C3+C4-C8 (result in cell G8)
Years and months: =ROUNDDOWN(G8/365,25;0)&" years and "&ROUND((G8/365,25-G9)*12;0)&" months"
Result: 13 years and 4 months

13. Nitin Verma says:

Simple pattern for plus and minus and datedif for year and month

=A4-A3-(A8-A7) =>4870

=DATEDIF(1,(A4-A3-(A8-A7)),"Y")&" Years "&DATEDIF(1,(A4-A3-(A8-A7)),"YM")&" Months" =>13 Years 4 Months

14. Sahil says:

Thanks, I will bookmark it and try it when I get back 😉

15. Peter B says:

I would first calculate the Julias's days of service (ServiceDays) as
= (CurrentDate - EmploymentStart + 1) - (LeaveEnd - LeaveStart + 1)
Complete years service would be
= FLOOR( ServiceDays, DaysPerYear ) / DaysPerYear
with a balance of days (AdditionalDays)
= MOD( ServiceDays, DaysPerYear )
To avoid clutter that could be a Named formula.
The additional days equate to completed months
= FLOOR( AdditionalDays, DaysPerMonth ) / DaysPerMonth
where DaysPerMonth is given by
= DaysPerYear / MonthsPerYear

16. Rishi Kumar says:

=DAYS(C4,C3)-DAYS(C8,C7)&" Days"

17. Jay Krishnam says:

Both (Continuous service days) & (Continuous service in years and months) can be found out by using a single formula DATEDIF

=DATEDIF("1-1-04","8-12-17","d")-DATEDIF("3-3-07","9-10-07","d") will give 4,870

=DATEDIF("1-1-04","8-12-17","y")-DATEDIF("3-3-07","9-10-07","y") will give 13

=DATEDIF("1-1-04","8-12-17","ym")-DATEDIF("3-3-07","9-10-07","ym") will give 4

18. Ajay Jaiswal says:

=DATEDIF(B1,B2,"y")-DATEDIF(D2,D3,"y")&"years,"&DATEDIF(B1,B2,"Ym")-DATEDIF(D2,D3,"Ym")&"Months"

19. Ajay Jaiswal says:

for days: =DATEDIF(B1,B2,"d")-DATEDIF(D2,D3,"d")

 5 conditional formatting top tips – Excel basics Merry Christmas and Happy New Year 2018 [Holiday Card]