# Help with calculating shift times

**Billy Russell**Oct 5, 2016 3:42 PM

So the question is pretty basic but with a wrinkle thrown in. I want to create a field based on a date field of what shift it took place on. Shifts are:

*1st: 7am to 3pm*

* 2nd: 3pm to 11pm *

*3rd: 11pm to 7am*

I was easily able to whip up a calculation for this:

*IF DATEPART('hour',[Opened]) >= 7 AND DATEPART('hour',[Opened]) < 15 THEN 'First Shift'*

*ELSEIF DATEPART('hour',[Opened]) >= 15 and datepart('hour',[Opened]) < 23 THEN 'Second Shift'*

*ELSE 'Third Shift' END*

Then my boss threw me the first curveball. He wanted to separate by weekday and weekend. Seemed easy enough so I threw this together:

First I made a day of week field:

*DATENAME('weekday',[Opened])*

Then I created a Part of Week field:

**IF [DayofWeek]="Saturday" THEN "Weekend"**

**ELSEIF [DayofWeek]="Sunday" then "Weekend"**

**ELSE "Weekday"**

**END**

All of that was relatively easy. Then my boss decided he wanted to see the data with a bit more granularity. So he wants separated charts showing weekend and weekday work separated into shifts. That's when I realized my original shift calculation wasn't entirely correct. Because I work Mon-Fri I forget that the weekend people have a different shift schedule. So the full shifts are as follows:

*Monday through Friday:*

*1st: 7am to 3pm*

*2nd: 3pm to 11pm *

*3rd: 11pm to 7am*

*Saturday and Sunday*

*1st: 7am to 7pm*

*3rd: 7pm to 7am*

And this is where I'm running into problems. Tableau doesn't seem to like nested functions too much. So I started attempting to hash out what my code needs to do. First I'll put it in pseudocode:

*If the day is Saturday or Sunday between the hours of 7am or 7pm then it's 1st shift,*

*If the day is Monday, Tuesday, Wednesday, Thursday or Friday between the hours of 7am and 3pm it's 1st shift,*

*If the day is Monday, Tuesday, Wednesday, Thursday or Friday between the hours of 3pm and 11pm it's 2nd shift,*

*The rest of the times are 3rd shift.*

So since I wasn't sure exactly how to do this I started off with Saturday. I put together the following bit of code just to see if this method would work:

**IF (([DayofWeek]="Saturday") AND ((DATEPART('hour',[Opened])>=7) OR (DATEPART('hour',[Opened])<19))) THEN "First Shift"**

**ELSEIF (([DayofWeek]="Saturday") AND ((DATEPART('hour',[Opened])<7) OR (DATEPART('hour',[Opened])>=19))) THEN "Third Shift"**

**ELSE "Other"**

**END**

Short answer: It doesn't work. When I look at the data itself it's only matching on the day and labeling anything on Saturday as "First Shift". It's essentially ignoring the AND modifier. From what I've read I would think this would work (unless I'm missing some formatting) but it doesn't. So this is where I am. If anyone can point out any mistakes or show me a better way to do any of this that would be great. I'm still new to Tableau and just feeling around.