Sick pay entitlement based on length of service.

Hi,

New Joiner, Im hoping someone can help me please,

I need to track and log individual staff members company sickpay entitlement and have it auto update based on their length of service.

I have added the staff members start date then I have used the dateif function to calculate year,months,days of service. I'm looking to have the Total days Entitlement column H13 & H14 to update automatically using the number of weeks full pay or half pay/length of service chart. the example has over 3 years service so should show the maximum entitlement of 6 weeks full and 6 weeks half pay ideally id like the entitlement to be calculated in days based on how many days per week they use ie in the example they work 5 days per week so 1 weeks entitlement is 5 days multiply that by 6 and the figure would be 30 days. im aware the formula would need to reference the normal working days column as well as the entitlement chart but can not wrap my head round how to do this as well as use the length of service. it would also need to auto update when a newer member of staff jumped from one entitlement to the next ie SSP to less than 1 years service then again when it jumped to one-two years service and so on until maximum entitlement is met. I would then need the Days entitlement used to auto update when a new absence is entered using the Number of days off column C. ideally id like it to flag when all of the full pay days had been used and when the Half pay days had been used but am completely confused on how to do this. is this possible. the example also uses a rolling 12 month period

Thank you in advance

Thank you so much I do apologise this is all new to me. I have updated my profile with the information you advised. Please do let me know if there is anything else i have missed or done incorrectly. First time forum user.

Do you calculate the whole "sick episode" within the 12 month period as within the last 12 months as long as the Last Day Absence is within the last 12 months?

Example: If today's date is Wednesday, March 11, 2024. Sick Episode is from Thursday, March 9, 2023 - Monday, March 13, 2023, which is 3 business days. However, from 1 year ago from today it would be only 1 day. Should all 3 days count since it's one episode or should you just count the days from today back 1 year?

If the later, then I think you have your solution.

If only 1 day should be counted, I'd recommend adding another column in the Excel Table called "Days in Last Year", with formula of:

Please Login or Register  to view this content.

Then in C8, you could just put:

Please Login or Register  to view this content.

This works perfectly thank you so much.

JeteMc I'm sorry I've come up against a problem, related to the above spreadsheet, I have an employee who jumped from SSP to the first lot of Paid sick pay on 5th March 2024 the entitlement updated perfectly but within the last 12 months they had used 5 days sickness this then showed up as them using the full entitlement of paid sick pay however this is not correct as all sickness absences were previous to 5th March 2024. for the entitlement used column i would need it to discount any SSP days used before the paid entitlement began. is this even possible? so the start date would be 05/09/2023 and any absence logged before they were entitled to paid sickpay from 05/03/2024 I would still need to log but it shouldn't be counted in the days entitled used G18? Thank you in advance for your help with this

Hi Apologies, Please see attached spreadsheet.

in the example the Employee Jumped from SSP only to 1 week full pay 1 week half pay from 05/03/2024 (6 months after their start date) for reference I have manually entered this date in cell F3.
The total days entitlement in H13 & I13 updated correctly but as there were several SSP days used before the paid entitlement activated the Days entitlement used is not showing the figure accurately as
no paid sick pay was used after the entitlement was valid from 3rd March.
Is it possible to exclude the dates within the SSP period from the H13 & I13 cells calculation so only dates within the entitlement range are deducted.ie exclude any absences from the days entitlement used cells
that were dated between start date F2 and the date 6 months after their start date F3. Ideally id like the 6th months date to auto update when the Start date is entered it would save massive amounts of time.
any help you can give me with this would be massively appreciated.
Thankyou in advance.

JeteMc This looks to work great thank you so much for your help.