Hi all
I wonder if you can help me please. I have been tying myself in knots trying to figure out the formulas to working out how much sick pay someone has had in a 12 month rolling year, and how to generate triggers to pick up several issues.
I am a regular user of excel, and would put myself at intermediate level, but these formulas are outside of my previous experience. I have tried googling and experimenting with different formulas, but just can't seem to figure it out.
I have attached a template spreadsheet for you to look at. Basically what I am trying to do is automatically calculate the following (separately for full time employees and for part time employees using the following rules:
Sick Pay entitlements – Full Time employess (35 hrs per week, 7 hours per day)
Up to 6 months continuous service………………………SSP only
After 6 months continuous service but less than 2 years continuous service…........44 days at full pay, 44 days at half pay
After 2 years continuous service……..………66 days at full pay, 66 days at half pay
Sick Pay Entitlements - Part Time Employees (Pro-rata of the entitlements above)
Using working days (22 days = 1 month, therefore 44 days equals 2 months for a full time person on 35 hours per week, 7 hours per day(hourly sickness entitlement would be 308 hours at full pay and 308 hours sickness at half pay), however Y works 21 hours per week, so entitlement is : 308hrs/35*21 = 184.8 hours full pay, and 184.8 hours half pay in a rolling year (rounded up to nearest whole hour so 185 hours full pay, and 185 hours half pay
Triggers
Absence Indicator query for checking:
• any unauthorised absence (Trigger to say “Unauthorised Absence”)
• Eight continuous working days of absence (Trigger to say “8 continuous working days”)
• Four individual periods of sick leave in a 12 month rolling period (Trigger to say “4 periods of Absence in 12 months)
• Sick Pay running out/Ran Out (For both Full Pay and Half Pay) (Trigger to Say either “Full Pay running out, or Half Pay running out, depending which it is)
If anyone is able to help or give me some advice, I will really appreciate it. Thank you
Bookmarks