• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

prragasa

New Member
Hello, need help here:

73590

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?
 
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.
 
Back
Top