calculating regular hours and OT hours.

Hi Tommy,

Don't feel bad if you are having some difficulty with this programing task. It's not a basic one. The combined requirement for OT based on the 8-hour day and the 40-hour week makes it difficult.

I think this solution will work for you:

I'll give you the formulas for the first day of the week, and you can select them and Fill Down.

For F2:

=((TIMEVALUE(D)-TIMEVALUE(C))*24)-E

For G2:

=MIN(MAX(SUM(OFFSET($F$1, 0,0,ROW()))-40, 0), F2)

For H2:

=MAX(F-8, 0)

For I2:

=MAX(G2,H2)

For J2:

=MAX(F2-I2, 0)

If you want to send me an e-mail, I'll return my draft document to you for testing.

You don't mention the possibility of a starting time of day later than the stop time of day, as can happen if someone works past Midnight. If this situation can occur, we can cover it with some additional complexity.

Jerry

You will need some extra work columns, that can be hidden once you've set them up.

---

First, format your columns properly:

Start, stop, Breaks time: Date and Time, Date (none), Time (H:mm PM) (or whatever you prefer)

Hours (Regular, OT): Duration (h:m)

---

Extra columns:

Worked =IF(ISBLANK(Stop time), DURATION(,,0), $Stop time - $Start time)

Subtotal

The formula for the first cell is =Worked

If the Subtotal column ends up in column G, then the formula for the second cell is =G2+Worked

(just click in the cell above, and Numbers will fill it in for you)

Once you have entered the formulas in the first couple of rows, just drag them down to repeat in the rest of the column.

---

Also, create an extra table, with just a few rows, for permanent info,

e.g. Normal =DURATION(,,40)

where "Normal" is a heading, not a regular cell.

---

Regular =IF(Subtotal<=Normal, Subtotal, Subtotal-Normal)

OT =IF(Regular<Normal, DURATION(,,0), Subtotal-Regular)

---

It's a lot to take in if you are new to all of this. 🙂

Hi Tommyboy29

You were quick to read my response, but I edited it a couple of minutes later, so the update might be clearer now.

The answers to your questions are under the heading "Extra columns" 🙂