# Calculating regular hours, regular OT, night shift hours, and night shift OT

#### prragasa

##### New Member
Hello, need help here:

What formulas to use for K7, L7, M7, and N7?

K7 must reflect the number of hours worked during a regular shift between 6AM and 10PM (L7 for overtime after 8 hours). M7, meanwhile, for number of hours worked during night shift between 10:01PM and 5:59AM the next day (N7 for overtime done falling in that period).

Entire shift can run across regular to night shift, or vice versa.

Entries are entered as date + time. Thank you!

prragasa
Would You upload an Excel-file with a sample data which covers all normal cases
as well as
with expected results based Your given clear rules?
Do You want, as You've written, formulas only for those four cells?

Here's the Excel file with the sample data.

#### Attachments

• Rough.xlsx
13 KB · Views: 10
prragasa
Would You upload an Excel-file with a sample data which covers all normal cases
as well as
with expected results based Your given clear rules?
Do You want, as You've written, formulas only for those four cells?

Yes, the formulas only for those four cells. Thanks man!

prragasa
Do that sample file covers all normal cases?
... expected results?
... show clear rules?
If only for those four cells then ... of course, You won't use formulas anywhere else ...

prragasa
Do that sample file covers all normal cases?
... expected results?
... show clear rules?
If only for those four cells then ... of course, You won't use formulas anywhere else ...

Yes. I actually have the formulas for K column and L column.

What I don't have are the correct formulas that counts:
1. how many of the REG HOURS in K column is work performed during night shift: between 10PM and 6AM the next day (for M column)
2. how many of the REG OT in L column is worked performed during night shift (for column N)
Hope this clarifies everything.

What do "WRAP" and "CALL" represent ?

What do "WRAP" and "CALL" represent ?

Call is what time the crew is supposed to report and Wrap is basically Time Out.