Spreadsheet: 775287d1648985244-calculate-pay-rate-depending-on-the-time-of-day-help-1-etaf
Sheet: DATA

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | BH Dates
 |  |  | the missing time between cells (F) and (H) are unpaid |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022-01-03 00:00:00 | Monday | New Year’s Day (substitute day)
 |  |  | first half |  |  |  | second half |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Saturday | Sunday | Bank holidays |  |  | basic pay = £13 |  |  |  |  | 2022-04-15 00:00:00 | Friday | Good Friday
 | Day | Duty No | Board 1 |  | In | Out | Board 2 | In | Out | Regular hrs | Sunday Overtime hrs | Saturday Overtime hrs 13:00 | Saturday Overtime hrs | Sick hrs | Holiday hrs | Total |  |  |  |  |  |  |  | 13.5 | Before 13:00 |  |  |  | Saturday before 13:00 = 13.50 |  |  |  |  | 2022-04-18 00:00:00 | Monday | Easter Monday
 | Sunday | 6001 | A134 |  | 05:34:00 | 08:29:00 | A120 | 08:59:00 | 12:29:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 14 | After 13:00 | All Day |  |  | Saturday after 13:00 = £14 |  |  |  |  | 2022-05-02 00:00:00 | Monday | Early May bank holiday
 | Monday | 6002 | A146 |  | 04:35:00 | 08:45:00 | A159 | 09:15:00 | 11:40:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 14.77 |  |  | All Day |  | Sunday all day = £14 |  |  |  |  | 2022-06-02 00:00:00 | Thursday | Spring bank holiday
 | Tuesday | 6003 | A102 |  | 04:40:00 | 09:16:00 | A105 | 09:46:00 | 11:48:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022-06-03 00:00:00 | Friday | Platinum Jubilee bank holiday
 | Wednesday | 6004 | A201 |  | 05:05:00 | 08:36:00 | A141 | 09:06:00 | 12:14:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022-08-29 00:00:00 | Monday | Summer bank holiday
 | Thursday | 6005 | A115 |  | 05:29:00 | 09:41:00 | A157 | 10:11:00 | 12:48:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022-12-26 00:00:00 | Monday | Boxing Day
 | Friday | 6101 | A120 |  | 12:26:00 | 14:15:00 | A157 | 14:45:00 | 19:49:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | if I work on a Saturday I get paid £13.50 before 13:00 and £14 after 13:00 |  |  |  |  |  |  |  |  |  | 2022-12-27 00:00:00 | Tuesday | Christmas Day (substitute day)
 | Saturday | 6007 | A116 |  | 05:40:00 | 09:55:00 | A147 | 10:25:00 | 13:08:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | if I work on a Sunday I get paid £14 all day. |  |  |  |  |  |  |  |  |  |  | 2023
 |  |  |  |  |  |  |  |  | Total hrs |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2023-01-02 00:00:00 | Monday | New Year’s Day (substitute day)
 | if the layout needs to change its ok as this is my first attempt at anything like this |  |  |  |  |  |  |  | Hourly rate | 13 | 14 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2023-04-07 00:00:00 | Friday | Good Friday
 |  |  |  |  |  |  |  |  | Total pay |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2023-04-10 00:00:00 | Monday | Easter Monday
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | RATE | 14.77 | 14 | 14 | 13.5 | 13 |  | TOTAL |  |  |  |  |  |  |  |  | 2023-05-01 00:00:00 | Monday | Early May bank holiday
 | 1 | 2 | 3 |  | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |  | BH | Sunday | [NAME] | sat <13 | Mon-fri |  |  |  |  |  |  |  |  |  |  | 2023-05-29 00:00:00 | Monday | Spring bank holiday
 | Duty No | Shift | Board 1 | Date | Sign On | Start Time | Finish Time | Sign Off | Board 2 | Sign On | Start Time | Finish Time | Sign Off | [NAME] | Platform Time | [NAME] | Paid Time | Week-Num | BH | SU | S-13 | [NAME] | M-F | WW | Total |  |  |  |  |  |  |  |  | 2023-08-28 00:00:00 | Monday | Summer bank holiday
 | 6001 | Early | A134 | 2022-01-03 00:00:00 | 05:34:00 | 05:49:00 | 08:27:00 | 08:29:00 | A120 | 08:59:00 | 09:11:00 | 12:24 | 12:29 | 6.55 | 5.51 | 00:10:00 | 6.25 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2023-12-25 00:00:00 | Monday | Christmas Day
 | 6002 | Early | A146 | 2022-02-02 00:00:00 | 04:35:00 | 04:50:00 | 08:43:00 | 08:45:00 | A159 | 09:15:00 | 09:30:00 | 11:35 | 11:40 | 7.05 | 5.58 | 00:13:00 | 6.35 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2023-12-26 00:00:00 | Tuesday | Boxing Day
 | 6003 | Early | A102 | 2022-02-03 00:00:00 | 04:40:00 | 04:55:00 | 09:14:00 | 09:16:00 | A105 | 09:46:00 | 09:55:00 | 11:27 | 11:48 | 7.08 | 5.51 | 00:07:00 | 6.38
 | 6004 | Early | A201 | 2022-02-04 00:00:00 | 05:05:00 | 05:20:00 | 08:34:00 | 08:36:00 | A141 | 09:06:00 | 09:18:00 | 12:09 | 12:14 | 7.09 | 6.05 | 00:10:00 | 6.39
 | 6005 | Early | A115 | 2022-02-05 00:00:00 | 05:29:00 | 13:44:00 | 14:39:00 | 09:41:00 | A157 | 10:11:00 | 15:26:00 | 18:26:00 | 12:48 | 7.19 | 5.55 | 00:13:00 | 6.49
 | 6006 | Early | A121 | 2022-02-06 00:00:00 | 08:16:00 | 08:40:00 | 12:13:00 | 12:15:00 | A104 | 12:45:00 | 13:08:00 | 15:32 | 15:37 | 7.21 | 5.57 | 00:21:00 | 6.51
 | 6007 | Early | A116 | 2022-02-07 00:00:00 | 05:40:00 | 05:55:00 | 09:53:00 | 09:55:00 | A147 | 10:25:00 | 10:47:00 | 12:35 | 13:08 | 7.28 | 5.46 | 00:20:00 | 6.58
 | 6008 | Early | A140 | 2022-02-08 00:00:00 | 04:25:00 | 04:40:00 | 09:15:00 | 09:17:00 | A118 | 09:47:00 | 10:09:00 | 11:51 | 11:56 | 7.31 | 6.17 | 00:20:00 | 7.01
 | 6009 | Early | A138 | 2022-01-03 00:00:00 | 04:16:00 | 04:31:00 | 08:26:00 | 08:28:00 | A133 | 08:58:00 | 09:12:00 | 11:17 | 11:48 | 7.32 | 6 | 00:12:00 | 7.02
 | 6010 | Early | A160 | 2022-02-10 00:00:00 | 04:46:00 | 05:01:00 | 09:03:00 | 09:05:00 | A127 | 09:35:00 | 09:52:00 | 11:57 | 12:28 | 7.42 | 6.07 | 00:15:00 | 7.12
 | 6011 | Early | A158 | 2022-02-11 00:00:00 | 04:51:00 | 05:06:00 | 08:22:00 | 08:24:00 | A160 | 08:54:00 | 09:03:00 | 12:29 | 12:34 | 7.43 | 6.42 | 00:07:00 | 7.13
 | 6012 | Early | A113 | 2022-02-12 00:00:00 | 05:24:00 | 11:00:00 | 13:11:00 | 09:13:00 | A116 | 14:43:00 | 16:00:00 | 17:00:00 | 13:08 | 7.44 | 6.3 | 00:08:00 | 7.14
 | 6013 | Early | A106 | 2022-02-13 00:00:00 | 04:56:00 | 05:11:00 | 08:30:00 | 08:32:00 | A113 | 09:02:00 | 09:11:00 | 12:36 | 12:41 | 7.45 | 6.44 | 00:07:00 | 7.15
 | 6014 | Early | A122 | 2022-02-14 00:00:00 | 05:01:00 | 05:16:00 | 09:27:00 | 09:29:00 | A129 | 09:59:00 | 10:16:00 | 12:17 | 12:48 | 7.47 | 6.12 | 00:15:00 | 7.17
 | 6015 | Early | A172A | 2022-02-15 00:00:00 | 05:29:00 | 05:44:00 | 10:36:00 | 10:38:00 | A123 | 11:08:00 | 11:40:00 | 13:13 | 13:18 | 7.49 | 6.25 | 00:13:00 | 7.19
 | 6016 | Early | A127 | 2022-02-16 00:00:00 | 05:12:00 | 05:27:00 | 09:52:00 | 09:54:00 | A131 | 10:24:00 | 10:47:00 | 12:59 | 13:04 | 7.52 | 6.37 | 00:21:00 | 7.22
 | 6017 | Early | A132 | 2022-02-17 00:00:00 | 03:55:00 | 04:10:00 | 08:47:00 | 08:49:00 | A115 | 09:19:00 | 09:39:00 | 11:12 | 11:48 | 7.53 | 6.1 | 00:18:00 | 7.23
 | 6018 | Early | A120 | 2022-02-18 00:00:00 | 04:21:00 | 04:36:00 | 09:11:00 | 09:13:00 | A144 | 09:43:00 | 10:06:00 | 12:11 | 12:16 | 7.55 | 6.4 | 00:21:00 | 7.25
 | 6019 | Early | A176A | 2022-02-19 00:00:00 | 06:08:00 | 06:54:00 | 08:54:00 | 08:23:00 | A135 | 09:26:00 | 14:00:00 | 16:59:00 | 14:04 | 7.56 | 6.51 | 00:11:00 | 7.26
 | 6020 | Early | A109 | 2022-02-20 00:00:00 | 05:56:00 | 06:11:00 | 08:23:00 | 08:25:00 | A140 | 08:55:00 | 09:15:00 | 13:49 | 13:54 | 7.58 | 6.46 | 00:18:00 | 7.28
 | 6021 | Early | A154 | 2022-02-21 00:00:00 | 04:55:00 | 05:10:00 | 08:51:00 | 08:53:00 | A126 | 09:23:00 | 09:37:00 | 12:52 | 12:57 | 8.02 | 6.56 | 00:12:00 | 7.32
 | 6022 | Early | A123 | 2022-02-22 00:00:00 | 05:08:00 | 05:23:00 | 08:38:00 | 08:40:00 | A149 | 09:10:00 | 09:21:00 | 13:11 | 13:16 | 8.08 | 7.05 | 00:09:00 | 7.38
 | 6023 | Early | A126 | 2022-02-23 00:00:00 | 05:39:00 | 05:54:00 | 09:37:00 | 09:39:00 | A107 | 10:09:00 | 10:21:00 | 13:15 | 13:48 | 8.09 | 6.37 | 00:10:00 | 7.39
 | 6024 | Early | A152A | 2022-02-24 00:00:00 | 06:24:00 | 06:39:00 | 09:19:00 | 09:21:00 | A130 | 09:51:00 | 10:17:00 | 14:29 | 14:34 | 8.1 | 6.52 | 00:10:00 | 7.4
 | 6025 | Early | A133 | 2022-02-25 00:00:00 | 04:57:00 | 05:12:00 | 09:12:00 | 09:43:00 | A137 | 10:13:00 | 10:29:00 | 12:37 | 13:08 | 8.11 | 6.08 | 00:14:00 | 7.41
 | 6026 | Early | A175A | 2022-02-26 00:00:00 | 05:56:00 | 06:11:00 | 09:34:00 | 09:36:00 | A153 | 10:06:00 | 10:46:00 | 12:46:00 | 14:08 | 8.12 | 6.23 | 00:15:00 | 7.42
 | 6027 | Early | A129 | 2022-02-27 00:00:00 | 05:20:00 | 05:35:00 | 10:16:00 | 10:18:00 | A139 | 10:48:00 | 11:07:00 | 13:29 | 13:34 | 8.14 | 7.03 | 00:17:00 | 7.44
 | 6028 | Early | A139 | 2022-02-28 00:00:00 | 04:36:00 | 04:51:00 | 08:47:00 | 08:49:00 | A114 | 09:19:00 | 09:32:00 | 12:45 | 12:50 | 8.14 | 7.09 | 00:11:00 | 7.44
 | 6029 | Early | A155 | 2022-03-01 00:00:00 | 04:40:00 | 04:55:00 | 08:59:00 | 09:01:00 | A104 | 09:31:00 | 09:46:00 | 12:51 | 12:56 | 8.16 | 7.09 | 00:13:00 | 7.46
 | 6030 | Early | A142 | 2022-03-02 00:00:00 | 06:25:00 | 06:40:00 | 11:35:00 | 11:37:00 | A157 | 12:07:00 | 12:26:00 | 14:38 | 14:43 | 8.18 | 7.07 | 00:17:00 | 7.48
 | 6031 | Early | A121 | 2022-03-03 00:00:00 | 05:10:00 | 05:25:00 | 08:40:00 | 08:42:00 | A122 | 09:12:00 | 09:27:00 | 13:24 | 13:29 | 8.19 | 7.12 | 00:13:00 | 7.49
 | 6032 | Early | A124 | 2022-03-04 00:00:00 | 05:12:00 | 05:27:00 | 10:32:00 | 10:34:00 | A133 | 11:04:00 | 11:17:00 | 13:29 | 13:34 | 8.22 | 7.17 | 00:11:00 | 7.52
 | 6033 | Early | A128 | 2022-03-05 00:00:00 | 06:24:00 | 06:39:00 | 11:47:00 | 11:49:00 | A158 | 12:19:00 | 12:36:00 | 14:48 | 14:53 | 8.29 | 7.2 | 00:15:00 | 7.59
 | 6034 | Early | A157 | 2022-03-06 00:00:00 | 05:16:00 | 05:31:00 | 10:26:00 | 10:28:00 | A134 | 10:58:00 | 11:17:00 | 13:17 | 13:48 | 8.32 | 6.55 | 00:17:00 | 8.02
 | 6035 | Early | A141 | 2022-03-07 00:00:00 | 04:15:00 | 04:30:00 | 09:18:00 | 09:20:00 | A136 | 09:50:00 | 10:17:00 | 12:17 | 12:48 | 8.33 | 6.48 | 00:25:00 | 8.03
 | 6036 | Early | A130 | 2022-03-08 00:00:00 | 05:35:00 | 05:50:00 | 10:17:00 | 10:19:00 | A103 | 10:49:00 | 11:03:00 | 14:03 | 14:08 | 8.33 | 7.27 | 00:12:00 | 8.03
 | 6037 | Early | A161 | 2022-03-09 00:00:00 | 03:56:00 | 04:11:00 | 08:33:00 | 08:35:00 | A102 | 09:05:00 | 09:14:00 | 12:27 | 12:32 | 8.36 | 7.35 | 00:07:00 | 8.06
 | 6038 | Early | A104 | 2022-03-10 00:00:00 | 05:29:00 | 05:44:00 | 09:46:00 | 09:48:00 | A124 | 10:18:00 | 10:32:00 | 13:32 | 14:08 | 8.39 | 7.02 | 00:12:00 | 8.09
 | 6039 | Early | A149 | 2022-03-11 00:00:00 | 04:35:00 | 04:50:00 | 09:21:00 | 09:23:00 | A162 | 09:53:00 | 10:17:00 | 13:09 | 13:14 | 8.39 | 7.23 | 00:22:00 | 8.09
 | 6040 | Early | A162 | 2022-03-12 00:00:00 | 05:50:00 | 09:00:00 | 10:00:00 | 10:19:00 | A115 | 10:49:00 | 12:24:00 | 14:24 | 14:29 | 8.39 | 7.24 | 00:21:00 | 8.09
 | 6041 | Early | A164 | 2022-03-13 00:00:00 | 06:24:00 | 06:39:00 | 10:16:00 | 10:18:00 | A991 | 10:48:00 | 11:01:00 | 15:01 | 15:06 | 8.42 | 7.37 | 00:11:00 | 8.12
 | 6042 | Early | A117 | 2022-03-12 00:00:00 | 06:04:00 | 13:19:00 | 15:30:00 | 11:32:00 | A136 | 12:02:00 | 16:17:00 | 20:17:00 | 14:48 | 8.44 | 7.11 | 00:13:00 | 8.14
 | 6043 | Early | A136 | 2022-03-15 00:00:00 | 05:49:00 | 06:04:00 | 10:17:00 | 10:19:00 | A156 | 10:49:00 | 11:16:00 | 14:28 | 14:33 | 8.44 | 7.25 | 00:25:00 | 8.14
 | 6044 | Early | A148 | 2022-03-16 00:00:00 | 05:16:00 | 05:31:00 | 09:06:00 | 09:08:00 | A143 | 09:38:00 | 09:57:00 | 13:35 | 14:08 | 8.52 | 7.13 | 00:17:00 | 8.22
 | 6045 | Early | A159 | 2022-03-17 00:00:00 | 05:31:00 | 05:46:00 | 09:30:00 | 09:32:00 | A145 | 10:02:00 | 10:15:00 | 14:00 | 14:28 | 8.57 | 7.29 | 00:11:00 | 8.27
 | 6046 | Early | A153 | 2022-03-18 00:00:00 | 05:50:00 | 06:05:00 | 10:46:00 | 10:48:00 | A105 | 11:18:00 | 11:27:00 | 14:15 | 14:48 | 8.58 | 7.29 | 00:07:00 | 8.28
 | 6101 | Middle | A120 | 2022-03-19 00:00:00 | 12:26:00 | 12:33:00 | 14:13:00 | 14:15:00 | A157 | 14:45:00 | 15:10:00 | 19:44 | 19:49 | 7.23 | 6.14 | 00:23:00 | 6.53
 | 6102 | Middle | A121 | 2022-03-20 00:00:00 | 12:23:00 | 12:30:00 | 17:08:00 | 17:10:00 | A150P | 17:40:00 | 17:53:00 | 19:45 | 19:50 | 7.27 | 6.3 | 00:11:00 | 6.57