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.

Please post your answers in comments.

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.

Answers for the example above:

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

Go ahead and post your formulas in comments. Julia is waiting for her bonus.

Written by Chandoo
Tags: , , , ,
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"

    • Khurram Shahzad says:

      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

  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")

Leave a Reply