Spreadsheet: 744708d1629386657-using-formula-to-calculate-day-and-night-shift-rates-of-pay-example-datset-to-calculate-day-and-night-charge
Sheet: E.G. data set using IF formulas

Date | Start | End | Staff | Booked Hours | BH - Less Break | Day hours | Night hours | Helper columns1 | Helper columns2 | FTE | Working Day of week | AD | Enhancement | Night>50% of day | Day rate | Sat/Night rate | Sun/BH rate | Day charge | Night charge | Total
2021-05-31 00:00:00 | 18:30:00 | 19:30:00 | Employee B | 02:00:00 | 01:00:00 | 01:00:00 | 00:00:00 | 05:00:00 | 11:00:00 | 0 |  |  | Holiday |  | 13.61024697609001 | 20.41537046413502 | 27.22049395218002
2021-05-10 00:00:00 | 10:00:00 | 18:00:00 | Employee A | 08:00:00 | 07:30:00 | 07:30:00 | 00:00:00 | 10:00:00 | 12:00:00 | 0 |  |  |  |  | 15.551165272983 | 23.32674790947449 | 31.10233054596599
2021-06-22 00:00:00 | 09:00:00 | 17:00:00 | Employee C | 08:00:00 | 07:30:00 | 07:30:00 | 00:00:00 | 11:00:00 | 11:00:00 | 0 |  |  |  |  | 21.35718255977496 | 32.03577383966244 | 42.71436511954992
2021-06-30 00:00:00 | 17:00:00 | 20:00:00 | Employee A | 03:00:00 | 03:00:00 | 03:00:00 | 00:00:00 | 03:00:00 | False | 0 |  |  |  |  | 15.551165272983 | 23.32674790947449 | 31.10233054596599
2021-07-29 00:00:00 | 11:30:00 | 13:30:00 | Employee B | 02:00:00 | 02:00:00 | 02:00:00 | 00:00:00 | 08:30:00 | 07:30:00 | 0 |  |  |  |  | 13.61024697609001 | 20.41537046413502 | 27.22049395218002
2021-07-30 00:00:00 | 09:00:00 | 17:00:00 | Employee C | 08:00:00 | 07:30:00 | 07:30:00 | 00:00:00 | 11:00:00 | 11:00:00 | 0 |  |  |  |  | 21.35718255977496 | 32.03577383966244 | 42.71436511954992
2021-07-17 00:00:00 | 09:00:00 | 10:00:00 | Employee A | 01:00:00 | 01:00:00 | 01:00:00 | 00:00:00 | 11:00:00 | 04:00:00 | 0 |  |  |  |  | 15.551165272983 | 23.32674790947449 | 31.10233054596599
2021-07-31 00:00:00 | 08:00:00 | 16:00:00 | Employee B | 08:00:00 | 07:30:00 | 07:30:00 | 00:00:00 | 12:00:00 | 10:00:00 | 0 |  |  |  |  | 13.61024697609001 | 20.41537046413502 | 27.22049395218002
2021-06-27 00:00:00 | 09:00:00 | 17:00:00 | Employee C | 08:00:00 | 07:30:00 | 07:30:00 | 00:00:00 | 11:00:00 | 11:00:00 | 0 |  |  |  |  | 21.35718255977496 | 32.03577383966244 | 42.71436511954992
2021-05-31 00:00:00 | 19:30:00 | 07:30:00 | Employee A | 12:00:00 | 11:30:00 | 02:00:00 | 09:30:00 | 00:30:00 | 01:30:00 | 0 |  |  | Holiday |  | 15.551165272983 | 23.32674790947449 | 31.10233054596599
2021-05-24 00:00:00 | 19:30:00 | 07:30:00 | Employee B | 12:00:00 | 11:30:00 | 02:00:00 | 09:30:00 | 00:30:00 | 01:30:00 | 0 |  |  |  |  | 13.61024697609001 | 20.41537046413502 | 27.22049395218002
2021-05-11 00:00:00 | 19:00:00 | 03:00:00 | Employee C | 08:00:00 | 07:30:00 | 01:00:00 | 06:30:00 | 01:00:00 | False | 0 |  |  |  |  | 21.35718255977496 | 32.03577383966244 | 42.71436511954992
2021-07-28 00:00:00 | 19:30:00 | 07:30:00 | Employee A | 12:00:00 | 11:30:00 | 02:00:00 | 09:30:00 | 00:30:00 | 01:30:00 | 0 |  |  |  |  | 15.551165272983 | 23.32674790947449 | 31.10233054596599
2021-05-27 00:00:00 | 19:30:00 | 07:30:00 | Employee B | 12:00:00 | 11:30:00 | 02:00:00 | 09:30:00 | 00:30:00 | 01:30:00 | 0 |  |  |  |  | 13.61024697609001 | 20.41537046413502 | 27.22049395218002
2021-07-23 00:00:00 | 21:00:00 | 09:00:00 | Employee C | 12:00:00 | 11:30:00 | 03:00:00 | 08:30:00 | False | 03:00:00 | 0 |  |  |  |  | 21.35718255977496 | 32.03577383966244 | 42.71436511954992
2021-05-22 00:00:00 | 19:30:00 | 07:30:00 | Employee A | 12:00:00 | 11:30:00 | 02:00:00 | 09:30:00 | 00:30:00 | 01:30:00 | 0 |  |  |  |  | 15.551165272983 | 23.32674790947449 | 31.10233054596599
2021-07-25 00:00:00 | 19:30:00 | 07:30:00 | Employee B | 12:00:00 | 11:30:00 | 02:00:00 | 09:30:00 | 00:30:00 | 01:30:00 | 0 |  |  |  |  | 13.61024697609001 | 20.41537046413502 | 27.22049395218002
[NAME] required to assist the following
Column Results
Col S | If Working Day Col (M) is not a Saturday or Sunday or Bank Holiday col (N)  and Night hours Col (H)are not more than Day hours Col( G) then result in Day Charge Col (S) is  Day hours Col G*Day rate col(P)
Col T | If Working Day Col (M) is not a Saturday or Sunday or Bank Holiday col (N)  and Night hours Col (H)are not more than Day hours Col( G) then result in Night  Charge Col (T) is  Night hours Col H*Day rate col(P)
Col S | If Working Day Col (M) is not a Sunday or Bank Holiday col (N)  and Night hours Col (H)are greater than 50% of Day hours Col (O) then result in Day Charge Col (S) is  Day hours Col G*Sat/night rate  rate col(Q)
Col T | If Working Day Col (M) is not a Sunday or Bank Holiday col (N)  and Night hours Col (H)are greater than 50% of Day hours Col (O) then result in Night Charge Col (T) is  Night hours [NAME] Sat/Night  rate col(Q)
Col S | if Working day Col (M) is a Sunday or Bank Holiday Col (N )  then Days Hours col (G) * Sun/BH rate Rate col R
Col T | if Working day Col (M) is a Sunday or Bank Holiday Col (N )  then Night  Hours col (H) * Sun/BH rate Rate col (R)