How to write formula for overtime pay

Question

I keep my own record of the hours worked, and I need help with the 6th day overtime formula.

After 5 days of 8 hours (40 hrs), the 6th day of work is all overtime. But this is not every week. Some weeks I get one day off during the weekday, in which the 6th day is counted as a 5th day and thus does not count as overtime.

So, for the 6th day, I want to write a formula that can have both of these conditions; calculate as regular 8 hours if I had one day off during the week, and also calculate as overtime if I worked all 5 days.

I've also added conditional formatting so that hours worked (whether reg or overtime) is blue colored, and hours not counted are red colored. So, in the first week (1/26~2/1), for the hours worked on 1/31 Saturday, since it is the 6th day after 5 days of work, I want the hours to show up as the OT hours in cell F8, but at the same time have the formula in the same cell in which if this day was NOT overtime, it could still return to me 8 hours as regular hours in cell E9.

And just for reference, the following are the formulas I already have in the above excel:

-Regular Hours cells (e.g. E3): =MIN((C3-B3)*24-D3,8)

-OT Hours cells (e.g. F3): =MAX(0,(C3-B3)*24-8-D3)

-Week Total Regular Hours (e.g. E10): =MIN(SUM(E3:E9),40)

-Week Total OT Hours (e.g. F10): =SUM(F3:F9)+MAX(0,SUM(E3:E9)-40)

I look forward to learning more of the magic of excel! Thank you in advance for looking into my inquiry and teaching me.

Answer 1

You shouldn't need to change your totals. Only change the daily Regular and OT formulas so they “know” whether this row is the 6th worked day or not.

Assume a blank In Time means a non-worked day.

Put these two formulas in every row (E and F), then copy down.

Regular Hours (E3):

=IF(COUNTIF($B$3:B3,">0:00")>5,
  0,
  MIN((C3-B3)*24-D3,8)
)

OT Hours (F3):

=IF(COUNTIF($B$3:B3,">0:00")>5,
  (C3-B3)*24-D3,
  MAX(0,(C3-B3)*24-8-D3)
)

COUNTIF($B$3:B3,">0:00") counts how many days so far in the week have hours worked

If more than 5 → this day is automatically overtime

If 5 or fewer → use your normal split regular vs OT logic

Blank days do not count, so if you take a weekday off, Saturday becomes the 5th day → no OT If you work all five weekdays, Saturday becomes the 6th → all OT

Your week totals continue to work the same way as before with no changes needed.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin