Forum Discussion
Solved
formula to match Day Shift and night shift for each day of the week based on time and dates
So I have a list of Start Dates, Start Times, End dates and end times (below) , and I basically need a formula that will look up cell, and match it with the correct shift based on looking up the star...
clh_1496 Quite a few odd dates towards the end in your list (start Jun 1 2022, end 0 January 1900. So let's ignore those. Perhaps the picture below helps you resolve your issue. If not, please clarify.
clh_1496 Quite a few odd dates towards the end in your list (start Jun 1 2022, end 0 January 1900. So let's ignore those. Perhaps the picture below helps you resolve your issue. If not, please clarify.
That's just because they're blank as the guys in the shift haven't clocked off yet so its entered a error time in.
This formula works for almost all of them, but for some reason, there's a period in the early hours where it reads 30/05/2022 01.40 as Mon NS rather a continuation of Sun NS - assuming this is because it reads the date?
| Sun NS | 29/05/2022 | 20:16 | 29/05/2022 |
| Sun NS | 29/05/2022 | 20:20 | 29/05/2022 |
| Sun NS | 29/05/2022 | 22:00 | 30/05/2022 |
| Sun NS | 29/05/2022 | 22:00 | 29/05/2022 |
| Sun NS | 29/05/2022 | 23:01 | 30/05/2022 |
| Sun NS | 29/05/2022 | 23:40 | 30/05/2022 |
| Mon NS | 30/05/2022 | 01:40 | 30/05/2022 |
| Mon NS | 30/05/2022 | 01:42 | 30/05/2022 |
| Mon NS | 30/05/2022 | 01:50 | 30/05/2022 |
| Mon NS | 30/05/2022 | 02:00 | 30/05/2022 |
| Mon NS | 30/05/2022 | 02:05 | 30/05/2022 |
| Mon NS | 30/05/2022 | 02:23 | 30/05/2022 |
| Mon NS | 30/05/2022 | 02:27 | 30/05/2022 |
| Mon NS | 30/05/2022 | 02:30 | 30/05/2022 |
| Mon NS | 30/05/2022 | 02:47 | 30/05/2022 |
| Mon NS | 30/05/2022 | 03:30 | 30/05/2022 |
| Mon NS | 30/05/2022 | 04:43 | 30/05/2022 |
| Mon NS | 30/05/2022 | 05:10 | 30/05/2022 |
| Mon DS | 30/05/2022 | 06:40 | 30/05/2022 |