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.
- I've managed to make a small adjustment to the formula you provided and it works!! Thank you for your help!!
=IF(AND(V142>='Reference Sheet'!$C$14,V142<='Reference Sheet'!$C$12),TEXT(U142-1,"ddd")&" "&IF(AND(V142>='Reference Sheet'!$C$12,V142<'Reference Sheet'!$C$13),'Reference Sheet'!$D$12,'Reference Sheet'!$D$13),TEXT(U142,"ddd")&" "&IF(AND(V142>='Reference Sheet'!$C$12,V142<'Reference Sheet'!$C$13),'Reference Sheet'!$D$12,'Reference Sheet'!$D$13)) 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
