Timesheet OT Vacation Sick, etc

I have a timesheet I was able to create with the help of this forum. I have a little gliche that I was wondering if there is a solution for.
If a person works overtime, and uses a benefit on the last day of the week, the overtime splits wrong.
Currently, it will split the vacation or sick, whichever benefit is used, and put part of that into OT. (You will see what I mean by the example attached).

Is there a way to make it so that if any benefit is used (such as Vacation, Sick, Hoiday or Funeral) that those hours will remain in the regular column, and the overtime gets allocated to the account codes used prior to the benefit?

In the example attached, the VAC should be 8 hours regular, and the code above it, 6721 should be 32 hours regular time, 6 hours of OT.

Since so many employees tend to use a benefit on a Friday, this happens everytime there is OT and we have to redo the sheet. I was hoping there was a better solution.

Thank you for your help!

HEY....that looks awesome! You must be a wizard!
I will try it on some of the other complicated sheets to check all the options, but it apprears to work nicely on this one!
Thank you!

PS. FUN....you are right! It is a bit ironic that they used FUN for funeral....I commented on that when I started here as well.

I copied that formula to another person's timesheet. But once I do that, it does not put any of his time into overtime. I don't really understand the formula you added, so I don't know how I need to fix that. I attached the other one. Thanks again!!!!!

That makes sense! The only problem I have then, is that the formulas need to be in the blank spaces so it calculates as the employee fills in the form. It did that before with the "if this cell is blank, then blank, etc. But I can't seem to do it with this complicated formula. Wen I copy the formula down, I get this: 0:00 #VALUE! all the way down. The template is set now to go to about 4 pages since sometimes the techs do have timesheets that long for one week.

I appreciate your help very much! I am trying to learn to do more with excel and I am taking as many classes as I can. But this helps so much!

I ran into a bit of a snag that baffled me!
Take a look at the example you uploaded. Change the first line end time to 12:30. It makes the OT formula error out causing the whole sheet to error.
One of the employees called me because she was trying to use that time of 8 am to 12:30 pm. Not sure why it does that.
Thanks again for your help....I wish I had your knowledge!

I am having the same type of problem adding in Holiday/Vacation Time. I need the holiday and vacation time to be regular time and not calculated with overtime.
Basically holiday/vacation is not part of regular pay. if i have 20 hours of vacation then i would have to work an extra 20 hrs regular time before overtime kicks in. attached here is a copy of the spread sheet we use for our time cards. can anyone help me out with this??

TimeCards test.xlsx

how do i create my own thread? i have not found anyweres to start my own thread.