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

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

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

2. ## Re: Calculate night shift hours worked - working hours span the night shift start and

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.``

Regards,
Humac

3. ## Re: Calculate night shift hours worked - working hours span the night shift start and

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)

4. ## Re: Calculate night shift hours worked - working hours span the night shift start and end

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

5. ## Re: Calculate night shift hours worked - working hours span the night shift start and end

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)