I need help figuring out how to accomplish a desired calculation automatically from a timekeeping dump.
I have attached a sample file with timekeeping data for one employee to keep things simple. I would like to calculate a balance by employee id. The calculation is: in the first 30 hours an employee works they are allowed one hour of Leave. The timekeeping data also includes the leave time taken so that must be subtracted. There is a ceiling of 56 hours of leave. Account 5 and 8 represent worked time. Account 6 with a S pay multiple is the leave I'm trying to calculate for over 100 employees.
What's the best way to accomplish automation?
It seems that when an employee works, for example, three full weeks of 40 hours for a total of 120, they only get 3 hours leave. Even though 120 equals 4 times 30 hours. That means he gets 1 hour leave for 30 hours worked and the remaining 10 hours of the week don't count towards the leave balance. Correct?
And, likewise, if the employee works 24 hours and takes 16 hours leave in one week, zero leave is earned. Correct?
Having said that, to set a ceiling for the number of leave hours you can use the MIN function. For instance:
=MIN(56, calculation)
This will return the lower of 56 and the outcome of the calculation so you don't have to write:
=IF(U6<56,U6+S7-F7,56)
This will do:
=MIN(56, U6+S7-F7)
Although a weekly calculation, there is carry forward. As an example week 1 an employee works 40 hours. For the first 30 hours the get 1 hour of S leave. 10 hours carry forward to week 2. So in week 2 the employee works 20 hours and 10 carry forward hours equals 30 hours. This triggers a second hour of leave granted. We have other types of leave so those leave hours are not included in worked hours and are excluded from the worked hours. All leave types use account 6. Leave types are Holiday, Vacation, Bereavement, etc.
Still not clear to me. Sorry! Do you really set limits up to 56 hours on a weekly running total basis. For instance, employee 1 worked 2102 hours in 2024. Doesn't that earn him 70 hours leave for the year? He had 15 hours left from the year before and was absent during 90 hours. This means he exceeded the earned leave for 2024 by 5 hours. Please explain what the end result in the Design concept should be for Employee 1. I've changed it a bit in the attached file to something that seems logical to me.
Riny, Employees get different types of leave in the USA. The leave I'm trying to calculate is a special Federal Government mandated leave. It is in addition to Holiday Leave, Vacation Leave, etc. The "S" leave or SPECIAL Leave is denoted in Column E of the Sample Data and yes it's calculated on a weekly rolling basis. I am ignoring 2023 and prior for simplicity and pretending that the S carryforward at the beginning of 2024 was 15 hours and for 2025 the full 56 hours should have been carried forward. I believe you lumped all Leave in the 90 hours not just "S" Leave
The Executive Order says, "......must provide employees with 1 hour of S for every 30 hours they work, up to 56 hours of S"
I changed the formula to =-SUMIFS(Table1[Hours_Worked],Table1[Code],B5,Table1[Year],A2,Table1[Pay_Multiple],A1) and I received the correct result
Thank you
