Hi Guys, I post this as I couldn't find a specific solution to my issue.
I need to find a formula that can calculate the number of day shift and night shift hours in any given shift worked.
The various formulas I have come across and used do not work for all scenarios.
Day Shift Hours are: 06:00 to 18:00
Night Shift Hrs are: 18:00 to 06:00
Start Finish Day Hrs Night Hrs 13:30 19:30 4.5 1.5
The formulas should work for all possibilities and combination of hours such as:
08:00-13:00 (5 day - 0 night)
02:30-19:30 (12 day - 5 night)
23:30-08:00 (2 day - 6.5 night)
03:30-05:00 (0 day - 1.5 night)
16:00-07:00 (3 day - 12 night)
etc.
The issue is there are night shift hours BEFORE the start of the day shift, and there are night hours AFTER the day shifts and vice versa.
So someone who worked for instance 05:00 - 19:00 ... has 1 hour night shift before and after the 12hrs he does in the day ... how do I add those 1 hrs to get a sum of 2.
Not to mention the nightmares of when the start time is LARGER than the Finish Time ... such as 23:00-00:00
Any help would be greatly appreciated.
Kindest of Regards,
AK
Bookmarks