Hi everyone,
I hope you can help me out. I have watched numerous videos, read multiple forums and help pages and I can't figure out the formula I need.
My company have launched a new policy for absence management with new company sick pay entitlements and trigger points, I have been tasked with creating the absence tracker to reflect this for all employees.
I have attached what I have done so far, the part I am struggling with is the trigger points, if an employee like employee 9 is off on LTS it counts the occasions as multiple occasions as we do not enter data on the weekend dates but my formula will count this as multiple occasions I need to count as 1 occasion as the total days off in a year is correct.
I have tried changing the formula in the days off in a year so we enter the data on a Saturday and Sunday, as it gets the occasions right but then the total days off is too many, i tried to correct that by /365 * 260 but then that is wrong from people who are not on LTS.
Please can you help me find a formula to count the occasions as continuous if they present like the above? I may have to throw my laptop out of the window if no one can help me!
Thank you soooooo much in advance
Absence Tracker for Help.xlsx
Bookmarks