Menu
How much long service bonus to pay? [Homework]
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.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « 5 conditional formatting top tips – Excel basics | Merry Christmas and Happy New Year 2018 [Holiday Card] » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
22 Responses to “How much long service bonus to pay? [Homework]”
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"
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))"
Just noticed that I set my sheet up slightly differently to the post. C10 should read "=DATEDIF(C3,C4,"d")-DATEDIF(C7,C8,"d")"
@FirstTimePoster,
To my opinion - the Months calculation part can be shorten to:
=(MOD(((C4-C3)-(C8-C7))/365.25,1)*12)
Micky
FirstTimePoster: 13 years and 4 months
Michael: 13 years and 4.00000000000001 months
and with some arithmetic manipulation:
=MOD((C4-C3-C8+C7)/365.25,1)*12
Micky
I think this function works well.
=DATEDIF(B1,B2,"d")-DATEDIF(B4,B5,"d")
=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"
B12=C4-C3-(C8-C7)
B13=DATEDIF(1,B12,"Y") & " years and " & DATEDIF(1,B12,"YM") & " months"
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" ]
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"
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
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 ""IENT(MOD(B12,365.25),365.25/12)&" months"
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
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
Thanks, I will bookmark it and try it when I get back 😉
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
=DAYS(C4,C3)-DAYS(C8,C7)&" Days"
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
=DATEDIF(B1,B2,"y")-DATEDIF(D2,D3,"y")&"years,"&DATEDIF(B1,B2,"Ym")-DATEDIF(D2,D3,"Ym")&"Months"
for days: =DATEDIF(B1,B2,"d")-DATEDIF(D2,D3,"d")