Breaking up day of week and hours to define shifts

[villasenorbritt]

I have data that I need to break into three shifts. 

DAY SHIFT = Monday through Friday 5 AM - 5 PM

NIGHT SHIFT = Monday - Friday (into Saturday morning) 5 PM - 5 AM

WEEKEND: Saturday - Sunday (into Monday morning)   Time on this one will be Saturday morning starting at 5 AM to Monday morning at 5 AM

This is the formula I am trying:

Shifts = IF(

vwDowntime[timeonly2] >= TIME(5,00,00) && vwDowntime[timeonly2] < TIME(17,00,00) && vwDowntime[WeekDay] = 1 || 2 || 3 || 4 || 5, "DAY",

IF(vwDowntime[timeonly2] < TIME(5,00,00) && vwDowntime[WeekDay] = 2 || 3 || 4 || 5 || 6      ||     vwDownTime[timeonly2] >= TIME(17,00,00) && vwDownTime[WeekDay] = 1 || 2 || 3 || 4 || 5, "NIGHT",

"WEEKEND"))

What I am getting is nothing but day shift:

If anyone has suggestions on my formula or possibly an easier way to acheive my goal, please let me know. Appreciate any advice! 

[amitchandak]

@villasenorbritt , One way is to change the date in the column

New Date = if(timevalue([Datetime]) < time(5,0,0) , datevalue([Datetime]) -1, datevalue([Datetime]) )

[villasenorbritt]

@amitchandak I'm afraid that did not exactly answer my question. I should have specified that I already have a column that fixes the dates using a formula identicle to yours. I then turned them into day of the week but in number format, which is where the (DownTime[dayofweek]) is coming from. I need to know how to do my shifts. Can you please look at my formula and let me know where I went wrong?

[rsbin]

@villasenorbritt ,

My preference is to use the SWITCH function instead of nesting IF Statements.  Once you understand it, it becomes much cleaner and easier to work with.
I have started a statement which I hope you can work with:

Shift = SWITCH(
            TRUE(),
            [Weekday] IN {"Monday", "Tuesday" } && [Time] >= TIME(5,0,0 ) && [Time] <= TIME(17, 0, 0 ), "DAY",
             "Other" )

Weekday    Time           Shift

Monday	5:00:00 AM	DAY
Tuesday	7:00:00 PM	Other
Sunday	8:00:00 AM	Other

If you can follow this pattern, I hope you will be able to make it work.  Otherwise, please post a sample of your vwDowntime table and I can then expand the SWITCH Statement for you.

Regards,

[villasenorbritt]

@rsbin Thank for your reply. I'm not sure what the problem is, but to try your solution, I attempted the following formula:

ShiftsSwitch = SWITCH(

TRUE(),

[Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"} && vwDowntime[datetime] >= TIME(5,00,00) && vwDowntime[datetime] < TIME(17,00,00), 1,

[Day] IN {"Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"} && vwDowntime[datetime] < TIME(5,00,00) || [Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"} && vwDowntime[datetime] >= TIME(17,00,00), 2,

3)

Shifts that are running on a Tuesday morning at 10:30 AM are showing up as 2nd shift. Any suggestions?

[villasenorbritt]

@rsbin I appreciate your help so much! I was actually using a "timeOnly" column in my data that had the data type of Date/Time. Once I switched it over to time only, this formula did exactly what I was wanting it to, thanks to you! 

[villasenorbritt]

And for anyone who is curious,

@amitchandak formula will correct the shift behavior so that the data gets accounted for in the correct shift.  

I then extracted the day name from the Real Shift Date.

You can then use @rsbin formula to complete defining your shifts. I used the column that I extracted from the Real Shift Dates called "Real Shift Day Name" and a timeOnly column.

To avoid my mistakes, make sure that data types are correct! This is why the formula did not work at first.  

Below is my formula:

Shift = SWITCH(

TRUE(),

[Real Shift Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [timeonly] >= TIME(5,0,0 ) && [timeonly] < TIME(17, 0, 0 ), "1st Shift",

[Real Shift Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"} && [timeonly] < TIME(5,0,0 ) ||

[Real Shift Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [timeonly] >= TIME(17,0,0 ), "2nd Shift",

"Weekend" )

[rsbin]

@villasenorbritt ,

My pleasure...glad it finally worked out for you.

[rsbin · Accepted solution]

@villasenorbritt ,

Am unable to see why your function doesn't work.

I recreated mine below and seems to work just fine.

Shift = SWITCH(
            TRUE(),
            [Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] >= TIME(5,0,0 ) && [datetime] <= TIME(17, 0, 0 ), 1,
            [Day] IN {"Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] < TIME(5,0,0 ) || 
            [Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] > TIME(17,0,0 ), 2,
             3 )

ShiftDaydatetime

1	Monday	5:00:00 AM
2	Tuesday	7:00:00 PM
3	Sunday	8:00:00 AM
2	Wednesday	11:00:00 PM
2	Tuesday	5:30:00 PM
1	Tuesday	5:30:00 AM
1	Tuesday	10:30:00 AM

[rsbin]

@villasenorbritt ,

In a meeting for the next little bit.  Will have a closer look when I am able.