5 Replies Latest reply on Nov 29, 2016 5:25 AM by Shinichiro Murakami

# Help with calculating shift times

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.

• ###### 1. Re: Help with calculating shift times

Billy,

I think it's easier to shift 7 hours from the beginning.

[Date time - 7 hours]

with this, you don't need to consider the "Day" ( I mean date difference across 12:00 am)

[Shift]

if [Weekday]=1 or [Weekday]=7 then

(if datepart('hour',[Date time - 7 hours])<12 then "1st Shift"

elseif datepart('hour',[Date time - 7 hours])<24 then "3rd Shift" end)

else

(if datepart('hour',[Date time - 7 hours])<8 then "1st Shift"

elseif datepart('hour',[Date time - 7 hours])<16 then "2nd Shift"

elseif datepart('hour',[Date time - 7 hours])<24 then "3rd Shift"

END)

end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Help with calculating shift times

That did it. I'm not sure exactly where I was going wrong but your code accomplished what I needed it to. Thank you very much.

• ###### 3. Re: Help with calculating shift times

Sounds good.

I did not test your code, but suspect that the logic across the date difference at 12:00 am?

Thanks,

Shin

• ###### 4. Re: Help with calculating shift times

Hi Shin,

Please let me know how to achieve this. We have a **** timing like this.

S1 - 6AM to 6PM

S2 - 6PM to 6AM

S3 - 6PM to 12AM

My dashboard is not able to identify the next day time which time between 12.01AM to 6AM as 24 hrs ends by 12AM.

Can you help how to resolve this?

Regards,

Nandha

• ###### 5. Re: Help with calculating shift times

Nandhakumar,

The concept is same.  Just change "7" to "6".

I don't understand hidden difficulty though without seeing your data.

If you have your own issue, please open different post with attaching packaged workbook.

Thanks,

Shin

1 of 1 people found this helpful