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
Bookmarks