Calculate night shift hours worked - working hours span the night shift start and end

[Gratch]

I'd like to calculate the night shift hours worked. - have been trying to for days now!

Some workers have work hours that span both the Night -Starting time and -End times, some span accross only one of the night shift start/end times

I have night shift hours: B6 = Starting time (06:00 PM) and B7 = End time (06:00 AM) (hh:mm AM/PM - no dates)
I have working hours: C15 = Start (06:00) and D15 = End (22:00) (yyyy/mm/dd hh:mm)

I've tried:

=IF(OR((MEDIAN(0,D15-C15,D15-(A15+$B$7))>0),(MEDIAN(0,D15-C15,(A15+$B$7)-C15)>0)),(MEDIAN(0,D15-C15,(A15+$B$7)-C15)+MEDIAN(0,D15-C15,D15-(A15+$B$6))),"")
... which works great = 4:00

BUT if I had C15 = Start (2011/11/25 03:00:00 PM) and D15 = End (2011/11/26 09:00:00 AM) - hypothetically :-)
with the same formula = 15
... it should be 12:00

I'd like to carry on with life... please

[humacdeep]

Hi Gratch,

Welcome to the forum!! Try the below formula.

A2: 2011/11/25 03:00:00 PM
B2: 2011/11/26 09:00:00 AM
C2: Copy the below formula
U2: 18:00
T2: 6:00

Please Login or Register  to view this content.

This is provided by daddylonglegs...

Regards,
Humac

[daddylonglegs]

Try this version for night hours

=D15-C15-(DAY(C15)<>DAY(D15))*(B$6-B$7)-MEDIAN(MOD(D15,1),B$6,B$7)+MEDIAN(MOD(C15,1),B$6,B$7)

[mjane.tulin]

Hi there....

I would like to ask for help for computing night differential of our employees,

below are the example of our timesheet

dtr.jpg

[protonLeah]

mjane.tulin,
Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Do not post pictures of workbooks.