Timesheet Overtime Calculation

Can someone help me with a formula that will calculate regular hours based on time in, break in/out, and time out so that it rounds to the nearest .25 of an hour? I also need the calculation for overtime above the regular 8 hour work day.

Thank you!!

See attached. You will need to enable macros.

Is there a way to round within the calculation for each day? For example, instead of having a separate section that rounds and then calculates the hours for that day, an IF(OR) statement that rounds directly from the entry of In and Out time.

See attached file. The regular hours formula works fine for rounding, but the overtime has an error that I can't pinpoint. I need regular hours to calculate only up to 8 hours in a day, and OT would be anything over that of course!

Thank you!!

Hi,

I think this is what you are after, but not quite sure.

In cell d14 I put in:

=IF(D13<8,0,ROUND((((SUM((D10-D9)+(D12-D11))*24)-8)/0.25),0)*0.25)

That's perfect, thank you! I changed the < to a > and it worked to calculate the OT hours.

From what I can tell, it should stay a < sign, because if your regular hours are less than 8, it starts to tally up negative overtime. That's why I made it a > to begin with. Also, when I change it a >, then it fails to count any positive overtime amounts.

I added the < back in and when the hours are over 8 in a day the OT won't calculate correctly. The > allows it to calculate OT, at least most of the time. But when I enter time for less than 8 hours, I end up with errors. sample attached. You can also see the formulas I had previously in rows 13-14 columns F-J. I'm working off a template someone else created for me over a year ago, so I think the formula just has a missing element that I can't figure out.

The reason it doesn't calculate is I created the formulas on the assumption there would always be a break time (i.e. 2 time in's and 2 time outs). I think I've managed to account for this and by using a slightly simpler formula.

Let me know if it works for you.

That is exactly what I needed. Thank you so much for your help!!