Hi
I need a formula/s that will calculate the total number of hours worked for shifts using the 24 hr clock (military time).
For ease of typing and convenience I want to be able to enter the shift times as a continuous number without using the : between the hour & the minutes if this is possible. eg. 0600 instead of 06:00, 1800 instead of 18:00 etc.
The formula/s needs to be able to recognise the following shift patterns;
Shifts starting before 1800 hours that extend beyond 1800 hours on a weekday. I need to separate hours worked before and after 1800 in order to then apply a different pay rate before and after 1800 hrs.
Shifts that pass midnight (2400 hrs) e.g. 2200 to 0600 and at the same time recognise the change in day type e.g. weekday, Saturday & Sunday. This is because different shift penalties need to be applied for the pay calculations.
Examples of what i'm trying to achieve;
Example shift A) Monday 0900 - 1700 = 8 hours allocated to Monday all paid at normal time.
Example shift B) Monday 1000 - 2000 = 8 hours (1000-1800) paid at normal time & 2 hours (1800-2000) paid at night rates.
Example shift C) Monday 2200 - 0600 = 8 hours paid at night rates
Example shift D) Friday 2200 - 0600 = 2 hours (2200-2400) allocated to Friday paid at night rates & 6 hours (0000-0600) paid at Sat rates.
Example shift E) Saturday 2200 - 0600 = 2 hours (2200-2400) allocated to Saturday paid at Sat rates & 6 hours (0000-0600) paid at Sunday rates.
Example shift F) Sunday 2200-0600 = 2 hours (2200-2400) allocated to Sunday paid at Sun rates & 6 hours (0000-0600) allocated to Monday paid at weeknight rates.
I've attached a table depicting the above examples.
Untitled.png
I trust this all makes sense and is achievable.
Ribs
Bookmarks