Spreadsheet: 816523d1675683274-need-a-formula-to-calculate-overtime-based-on-a-custom-40-hour-week-excelotcalc
Sheet: J002P

 |  |  |  |  |  |  |  |  | DATE | WEEKDAY | CLOCK IN | CLOCK OUT | STANDARD | OVERTIME | ENTRY METHOD | WEEK START | WEEK END | TOTAL HOURS | COLUMN N EXAMPLE | COLUMN O EXAMPLE
 |  |  |  |  |  |  |  |  | 2022-12-30 00:00:00 |  | 2022-12-30 07:15:00 | 2022-12-30 12:28:00 |  |  | AUTO |  |  |  | 5.216666666558012 | 0
 |  |  |  |  |  |  |  |  | 2022-12-30 00:00:00 |  | 2022-12-30 13:28:00 | 2022-12-30 17:21:00 |  |  | AUTO |  |  |  | 3.883333333244082 | 0
 |  |  |  |  |  |  |  |  | 2023-01-02 00:00:00 |  | 2023-01-02 08:13:00 | 2023-01-02 10:05:00 |  |  | AUTO |  |  |  | 1.866666666639503 | 0
 |  |  |  |  |  |  |  |  | 2023-01-02 00:00:00 |  | 2023-01-02 13:52:00 | 2023-01-02 18:24:00 |  |  | AUTO |  |  |  | 4.533333333441988 | 0
 |  |  |  |  |  |  |  |  | 2023-01-03 00:00:00 |  | 2023-01-03 06:23:00 | 2023-01-03 12:06:00 |  |  | AUTO |  |  |  | 5.71666666661622 | 0
 |  |  |  |  |  |  |  |  | 2023-01-03 00:00:00 |  | 2023-01-03 13:55:00 | 2023-01-03 19:58:00 |  |  | AUTO |  |  |  | 6.049999999988358 | 0
 |  |  |  |  |  |  |  |  | 2023-01-04 00:00:00 |  | 2023-01-04 08:13:00 | 2023-01-04 12:26:00 |  |  | AUTO |  |  |  | 4.21666666661622 | 0
 |  |  |  |  |  |  |  |  | 2023-01-04 00:00:00 |  | 2023-01-04 13:42:00 | 2023-01-04 17:55:00 |  |  | AUTO |  |  |  | 4.216666666790843 | 0
 |  |  |  |  |  |  |  |  | 2023-01-05 00:00:00 |  | 2023-01-05 07:38:00 | 2023-01-05 12:08:00 |  |  | AUTO |  |  |  | 4.3 | 0.2
 |  |  |  |  |  |  |  |  | 2023-01-05 00:00:00 |  | 2023-01-05 14:43:00 | 2023-01-05 18:05:00 |  |  | AUTO |  |  |  | 0 | 3.4
 |  |  |  |  |  |  |  |  | 2023-01-06 00:00:00 |  | 2023-01-06 07:29:00 | 2023-01-06 12:46:00 |  |  | AUTO |  |  |  | 5.283333333441988 | 0
 |  |  |  |  |  |  |  |  | 2023-01-06 00:00:00 |  | 2023-01-06 13:13:00 | 2023-01-06 18:39:00 |  |  | AUTO |  |  |  | 5.433333333407063 | 0
 |  |  |  |  |  |  |  |  | 2023-01-07 00:00:00 |  | 2023-01-07 07:52:00 | 2023-01-07 12:08:00 |  |  | AUTO |  |  |  | 4.266666666779201 | 0
 |  |  |  |  |  |  |  |  | 2023-01-07 00:00:00 |  | 2023-01-07 14:47:00 | 2023-01-07 18:57:00 |  |  | AUTO |  |  |  | 4.166666666627862 | 0
 |  |  |  |  |  |  |  |  | 2023-01-09 00:00:00 |  | 2023-01-09 06:22:00 | 2023-01-09 12:50:00 |  |  | AUTO |  |  |  | 6.46666666661622 | 0
 |  |  |  |  |  |  |  |  | 2023-01-09 00:00:00 |  | 2023-01-09 13:01:00 | 2023-01-09 19:48:00 |  |  | AUTO |  |  |  | 6.783333333267365 | 0
 |  |  |  |  |  |  |  |  | 2023-01-10 00:00:00 |  | 2023-01-10 08:25:00 | 2023-01-10 12:40:00 |  |  | AUTO |  |  |  | 4.250000000058208 | 0
 |  |  |  |  |  |  |  |  | 2023-01-10 00:00:00 |  | 2023-01-10 13:34:00 | 2023-01-10 17:45:00 |  |  | AUTO |  |  |  | 3.3 | 0.8
 |  |  |  |  |  |  |  |  | 2023-01-11 00:00:00 |  | 2023-01-11 06:22:00 | 2023-01-11 12:31:00 |  |  | AUTO |  |  |  | 0 | 6.149999999965075
 |  |  |  |  |  |  |  |  | 2023-01-11 00:00:00 |  | 2023-01-11 13:00:00 | 2023-01-11 18:07:00 |  |  | AUTO |  |  |  | 0 | 5.116666666755918
 |  |  |  |  |  |  |  |  | 2023-01-12 00:00:00 |  | 2023-01-12 07:22:00 | 2023-01-12 12:35:00 |  |  | AUTO |  |  |  | 0 | 5.216666666732635
 |  |  |  |  |  |  |  |  | 2023-01-12 00:00:00 |  | 2023-01-12 13:36:00 | 2023-01-12 17:29:00 |  |  | AUTO |  |  |  | 0 | 3.883333333418705
 |  |  |  |  |  |  |  |  | 2023-01-13 00:00:00 |  | 2023-01-13 07:29:00 | 2023-01-13 10:18:00 |  |  | AUTO |  |  |  | 2.81666666676756 | 0
 |  |  |  |  |  |  |  |  | 2023-01-13 00:00:00 |  | 2023-01-13 13:53:00 | 2023-01-13 18:23:00 |  |  | AUTO |  |  |  | 4.5 | 0
 |  |  |  |  |  |  |  |  | 2023-01-16 00:00:00 |  | 2023-01-16 07:06:00 | 2023-01-16 12:08:00 |  |  | AUTO |  |  |  | 5.033333333500195 | 0
 |  |  |  |  |  |  |  |  | 2023-01-16 00:00:00 |  | 2023-01-16 13:00:00 | 2023-01-16 17:06:00 |  |  | AUTO |  |  |  | 4.100000000093132 | 0
 |  |  |  |  |  |  |  |  | 2023-01-17 00:00:00 |  | 2023-01-17 06:55:00 | 2023-01-17 12:28:00 |  |  | AUTO |  |  |  | 5.549999999930151 | 0
 |  |  |  |  |  |  |  |  | 2023-01-17 00:00:00 |  | 2023-01-17 13:10:00 | 2023-01-17 19:23:00 |  |  | AUTO |  |  |  | 6.216666666674428 | 0
 |  |  |  |  |  |  |  |  | 2023-01-18 00:00:00 |  | 2023-01-18 06:08:00 | 2023-01-18 10:56:00 |  |  | AUTO |  |  |  | 4.799999999930151 | 0
 |  |  |  |  |  |  |  |  | 2023-01-18 00:00:00 |  | 2023-01-18 13:52:00 | 2023-01-18 18:10:00 |  |  | AUTO |  |  |  | 4.300000000046566 | 0
 |  |  |  |  |  |  |  |  | 2023-01-19 00:00:00 |  | 2023-01-19 08:55:00 | 2023-01-19 12:58:00 |  |  | AUTO |  |  |  | 2.65 | 1.4
 |  |  |  |  |  |  |  |  | 2023-01-19 00:00:00 |  | 2023-01-19 13:17:00 | 2023-01-19 18:59:00 |  |  | AUTO |  |  |  | 0 | 5.7
 |  |  |  |  |  |  |  |  | 2023-01-20 00:00:00 |  | 2023-01-20 07:12:00 | 2023-01-20 12:19:00 |  |  | AUTO |  |  |  | 5.116666666581295 | 0
 |  |  |  |  |  |  |  |  | 2023-01-20 00:00:00 |  | 2023-01-20 13:18:00 | 2023-01-20 18:50:00 |  |  | AUTO |  |  |  | 5.533333333209157 | 0
 |  |  |  |  |  |  |  |  | 2023-01-23 00:00:00 |  | 2023-01-23 08:04:00 | 2023-01-23 11:25:00 |  |  | AUTO |  |  |  | 3.350000000093132 | 0
 |  |  |  |  |  |  |  |  | 2023-01-23 00:00:00 |  | 2023-01-23 13:05:00 | 2023-01-23 19:30:00 |  |  | AUTO |  |  |  | 6.416666666627862 | 0
 |  |  |  |  |  |  |  |  | 2023-01-24 00:00:00 |  | 2023-01-24 06:48:00 | 2023-01-24 12:54:00 |  |  | AUTO |  |  |  | 6.099999999976717 | 0
 |  |  |  |  |  |  |  |  | 2023-01-24 00:00:00 |  | 2023-01-24 14:29:00 | 2023-01-24 17:08:00 |  |  | AUTO |  |  |  | 2.649999999906868 | 0
 |  |  |  |  |  |  |  |  | 2023-01-25 00:00:00 |  | 2023-01-25 06:31:00 | 2023-01-25 12:24:00 |  |  | AUTO |  |  |  | 5.883333333476912 | 0
 |  |  |  |  |  |  |  |  | 2023-01-25 00:00:00 |  | 2023-01-25 13:05:00 | 2023-01-25 19:10:00 |  |  | AUTO |  |  |  | 5 | 1.1
 |  |  |  |  |  |  |  |  | 2023-01-26 00:00:00 |  | 2023-01-26 06:45:00 | 2023-01-26 11:23:00 |  |  | AUTO |  |  |  | 0 | 4.633333333418705
 |  |  |  |  |  |  |  |  | 2023-01-26 00:00:00 |  | 2023-01-26 13:24:00 | 2023-01-26 18:39:00 |  |  | AUTO |  |  |  | 0 | 5.25
 |  |  |  |  |  |  |  |  | 2023-01-27 00:00:00 |  | 2023-01-27 06:33:00 | 2023-01-27 12:12:00 |  |  | AUTO |  |  |  | 5.649999999906868 | 0
 |  |  |  |  |  |  |  |  | 2023-01-27 00:00:00 |  | 2023-01-27 13:07:00 | 2023-01-27 20:15:00 |  |  | AUTO |  |  |  | 7.133333333360497 | 0
 |  |  |  |  |  |  |  |  | 2023-01-30 00:00:00 |  | 2023-01-30 07:12:00 | 2023-01-30 12:50:00 |  |  | AUTO |  |  |  | 5.633333333185874 | 0
 |  |  |  |  |  |  |  |  | 2023-01-30 00:00:00 |  | 2023-01-30 13:11:00 | 2023-01-30 18:23:00 |  |  | AUTO |  |  |  | 5.200000000011642 | 0
 |  |  |  |  |  |  |  |  | 2023-01-31 00:00:00 |  | 2023-01-31 07:55:00 | 2023-01-31 12:49:00 |  |  | AUTO |  |  |  | 4.900000000081491 | 0
 |  |  |  |  |  |  |  |  | 2023-01-31 00:00:00 |  | 2023-01-31 13:32:00 | 2023-01-31 20:13:00 |  |  | AUTO |  |  |  | 6.683333333465271 | 0
 |  |  |  |  |  |  |  |  | 2023-02-01 00:00:00 |  | 2023-02-01 08:39:00 | 2023-02-01 12:07:00 |  |  | AUTO |  |  |  | 3.46666666661622 | 0
 |  |  |  |  |  |  |  |  | 2023-02-01 00:00:00 |  | 2023-02-01 13:32:00 | 2023-02-01 19:17:00 |  |  | AUTO |  |  |  | 1.4 | 4.3
 |  |  |  |  |  |  |  |  | 2023-02-02 00:00:00 |  | 2023-02-02 07:32:00 | 2023-02-02 12:12:00 |  |  | AUTO |  |  |  | 0 | 4.666666666686069
 |  |  |  |  |  |  |  |  | 2023-02-02 00:00:00 |  | 2023-02-02 13:53:00 | 2023-02-02 18:15:00 |  |  | AUTO |  |  |  | 0 | 4.366666666581295
 |  |  |  |  |  |  |  |  | 2023-02-03 00:00:00 |  | 2023-02-03 07:59:00 | 2023-02-03 10:59:00 |  |  | AUTO |  |  |  | 3 | 0
 |  |  |  |  |  |  |  |  | 2023-02-03 00:00:00 |  | 2023-02-03 13:35:00 | 2023-02-03 20:16:00 |  |  | AUTO |  |  |  | 6.7 | 0