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))
=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))