I have created a timesheet and I am having trouble calculating time when it meets certain criteria. Here is what I'm trying to accomplish.
I can only carry a maximum of 26 weeks worth of sick time (1040 hrs.). I am trying to use SUMIF or SUMIFS to check to see if my Sick Balance is >=1040:00; if so I don't want it to add the 3:05 I would earn in a pay period if the balance is less. If the Sick Balance is greater I just want it to carry the maximum amount of 1040:00 to the next balance column.
Once my Sick Balance goes below 1040:00, I want it to continue adding the 3:05 until the maximum is reached again and so on. My problem is I'm not sure if SUMIF or SUMIFS are my answers and if so how to phrase the calculations. If not; is there a way to accomplish this task. The following is an example of what I'm trying to accomplish.
Sick Balance 1040:36 1040:00 1040:00 1032:00 1035:05 1038:10 1040:00
Sick Earned 3:05 3:05 3:05 3:05 3:05 3:05
Sick Total 1043:41 1040:00 1040:00 1035:05 1038:10 1040:00
Sick Used 0:00 0:00 8:00 0:00 0:00 0:00
Thanks for any help.
