Assign a value based on time and specific day (2 different variables)

Question

I am trying to assign a specific working shift letter to a new column based on the time and day of the record in my sheet.

I created 2 new fields to make the calculation easier.

• Using my "date" column ("m/dd/yyyy"), I created a column called "WeekDay" that basically displays the day of every record with a number from 1 to 7 (1 = Monday 5=Friday, etc).
• Using my "time" column ("hh:mm AM/PM"), I created a column called "Hour of Day" that displays a number from 0 to 24 based on the time of my record (i.e:5:40 AM = 5)

The working shifts letters I want to use are as followed:

• A shift - Mon to Fri from 6 to 14 hrs.
• B shift - Mon to Fri from 14 to 22 hrs.
• C shift - Mon to Fri from 22 to 6 hrs.
• WD (Weekend Day) Shift - Sat and Sun from 6 to 18 hrs.
• WN (Weekend Night) Shift -Sat and Sun from 18 to 6 hrs.

Below is what I have done, the code works but only for Shifts A, B and C. I cannot make it work, so, it also includes WD and WN.

=IF(AND([@[Hour of Day]]>=6,[@[Hour of Day]]<14,OR([@WeekDay]=1,[@WeekDay]=2,[@WeekDay]=3,[@WeekDay]=4,[@WeekDay]=5)),"A",
IF(AND([@[Hour of Day]]<22,[@[Hour of Day]]>=14,OR([@WeekDay]=1,[@WeekDay]=2,[@WeekDay]=3,[@WeekDay]=4,[@WeekDay]=5)),"B",
IF(AND([@[Hour of Day]]>=22,[@[Hour of Day]]<6,OR([@WeekDay]=1,[@WeekDay]=2,[@WeekDay]=3,[@WeekDay]=4,[@WeekDay]=5)),"C",
IF(AND([@[Hour of Day]]>=6,[@[Hour of Day]]<18,OR([@WeekDay]=6,[@WeekDay]=7)),"WD",
IF(AND([@[Hour of Day]]>18,[@[Hour of Day]]>0,[@[Hour of Day]]<6,OR([@WeekDay]=6,[@WeekDay]=7)),"WN","C")))))

Answer 1

Your last IF condition is faulty. You have Hour > 18 and Hour < 6 which is never going to be met. You need to break it into two if statements. If Day = 6 or 7 and Hour > 18, then WN, else if Day = 7 or 1 and Hour < 6, then

=IF(AND([@[Hour of Day]]>=6,[@[Hour of Day]]<14,OR([@Weekday]=1,[@Weekday]=2,[@Weekday]=3,[@Weekday]=4,[@Weekday]=5)),"A",
IF(AND([@[Hour of Day]]<22,[@[Hour of Day]]>=14,OR([@Weekday]=1,[@Weekday]=2,[@Weekday]=3,[@Weekday]=4,[@Weekday]=5)),"B",
IF(AND([@[Hour of Day]]>=22,[@[Hour of Day]]<6,OR([@Weekday]=1,[@Weekday]=2,[@Weekday]=3,[@Weekday]=4,[@Weekday]=5)),"C",
IF(AND([@[Hour of Day]]>=6,[@[Hour of Day]]<18,OR([@Weekday]=6,[@Weekday]=7)),"WD",
IF(AND([@[Hour of Day]]>=18,OR([@Weekday]=6,[@Weekday]=7)),"WN",
IF(AND([@[Hour of Day]]<6,OR([@Weekday]=1,[@Weekday]=7)),"WN","C"))))))

UPDATE

Taking into account the flaws in your original formula pointed out by @Jaquez, I worked out this formula.

=IF((FLOOR((([@Weekday]*24+[@[Hour of Day]]-6)+7*24)/24,1))<6,CHOOSE(ROUNDUP([@[Hour of Day]]/8,0),"A","B","C"),IF((MOD([@S2],24))<12,"WD","WN"))

I am calculating the duration in hours from the start of the week and offsetting it by 6 to eliminate overlap of C shift across days. Then I am using mode to split the shifts to 8 hour shifts during weekdays and 12 hour shifts during weekends.

Doing this in a hurry and so let me know if you need a more detailed explanation.

Answer 2

Like @eshwar mentioned, the main problem is that AND(Hour>=22,Hour<6) will never be true. Change that to OR(Hour<6,Hour>=22) and you're mostly there. But if you follow along below, you can remove it altogether to eliminate the confusion.

First, rather that repeating yourself, I would pull the day of the week out into a parent IF to simplify the logic. And rather than checking the weekday against every single possible value, just check if Weekday<=5 and you have save yourself even more.

This also makes Weekday>=6 a given in the [value_if_false] part of the parent IF since anything that makes it there is already a weekend and you can just check the if the time is AND(Hour>=6,Hour<18) for "WD".

This leaves the same original problem for "WN", namely OR(Hour<6,Hour>=18) instead of AND, but since this is, again, the only remaining option, you can omit that as well and just make "WN" the [value_if_false] part.

I've used indenting to show nesting:

=IF(WeekDay<=5,
    IF(AND(Hour_of_Day>=6,Hour_of_Day<14),"A",
       IF(AND(Hour_of_Day>=14,Hour_of_Day<22),"B","C")),
    IF(AND(Hour_of_Day>=6,Hour_of_Day<18),"WD","WN"))

Which produces this:

    1   2   3   4   5   6   7
0   C   C   C   C   C   WN  WN
1   C   C   C   C   C   WN  WN
2   C   C   C   C   C   WN  WN
3   C   C   C   C   C   WN  WN
4   C   C   C   C   C   WN  WN
5   C   C   C   C   C   WN  WN
6   A   A   A   A   A   WD  WD
7   A   A   A   A   A   WD  WD
8   A   A   A   A   A   WD  WD
9   A   A   A   A   A   WD  WD
10  A   A   A   A   A   WD  WD
11  A   A   A   A   A   WD  WD
12  A   A   A   A   A   WD  WD
13  A   A   A   A   A   WD  WD
14  B   B   B   B   B   WD  WD
15  B   B   B   B   B   WD  WD
16  B   B   B   B   B   WD  WD
17  B   B   B   B   B   WD  WD
18  B   B   B   B   B   WN  WN
19  B   B   B   B   B   WN  WN
20  B   B   B   B   B   WN  WN
21  B   B   B   B   B   WN  WN
22  C   C   C   C   C   WN  WN
23  C   C   C   C   C   WN  WN

Hopefully this the formatting and brevity make it easier to understand, decreasing the chances of logic errors.

Update: Darn inability to let it go!

This covers the instance where Shift C works Friday nights in to Saturday and Shift WN works Sunday nights into Monday:

=IF(WeekDay<=5,
    IF(AND(Hour_of_Day>=6,Hour_of_Day<14),"A",
       IF(AND(Hour_of_Day>=14,Hour_of_Day<22),"B",
          IF(AND(WeekDay=1,Hour_of_Day<6),"WN","C"))),
    IF(AND(Hour_of_Day>=6,Hour_of_Day<18),"WD",
       IF(AND(WeekDay=6,Hour_of_Day<6),"C","WN")))

Which produces this:

    1   2   3   4   5   6   7
0   WN  C   C   C   C   C   WN
1   WN  C   C   C   C   C   WN
2   WN  C   C   C   C   C   WN
3   WN  C   C   C   C   C   WN
4   WN  C   C   C   C   C   WN
5   WN  C   C   C   C   C   WN
6   A   A   A   A   A   WD  WD
7   A   A   A   A   A   WD  WD
8   A   A   A   A   A   WD  WD
9   A   A   A   A   A   WD  WD
10  A   A   A   A   A   WD  WD
11  A   A   A   A   A   WD  WD
12  A   A   A   A   A   WD  WD
13  A   A   A   A   A   WD  WD
14  B   B   B   B   B   WD  WD
15  B   B   B   B   B   WD  WD
16  B   B   B   B   B   WD  WD
17  B   B   B   B   B   WD  WD
18  B   B   B   B   B   WN  WN
19  B   B   B   B   B   WN  WN
20  B   B   B   B   B   WN  WN
21  B   B   B   B   B   WN  WN
22  C   C   C   C   C   WN  WN
23  C   C   C   C   C   WN  WN