2 Replies Latest reply on Jul 26, 2018 3:35 PM by Don Wise

# How to calculate 3 Shifts a day

hi every one,

i have a time table as belows:

i want to calculate 3 Shifts a day: shift 1: from 06:00 to 14:20; shift 2: from 14:20 to 22:30; shift 3: from 22:30 to 06:00 of the next day. the result i want to have is:

can you please help me with the calculation?.  i have tested some calc but it gave me not correct results.

Thankyou

• ###### 1. Re: How to calculate 3 Shifts a day

Hi Phalondon,

You can try

IF DATEPART('hour',[Time]) >= 6 AND DATEPART('hour',[Time]) < 14.20   THEN '1st Shift'

ELSEIF DATEPART('hour',[Time]) >= 14.20 and DATEPART('hour',[Time]) < 22:30 THEN '2nd Shift'

ELSE 'Third Shift' END

Just replace the ' ... ' with what ever you want to display. ie. IF DATEPART('hour',[Time]) >= 6 AND DATEPART('hour',[Time]) < 14.20   THEN 'from 6  to 14:20' and so on.

Also, check your time column- some data formatting/ transformation might be required for tableau to recognize this as a time. But once that is done- the calculation should ideally work.

Thanks,

Soham

• ###### 2. Re: How to calculate 3 Shifts a day

Hello,

You can try the attached Tableau workbook or look at the below screenshot.  Your time/date values need better formatting and to ensure that there were enough values I added a few more dates/times to ensure they grouped appropriately.  Your Excel worksheet is reattached as well with the new date/time values.  If this answers your question, please mark this response as correct.  Thx, Don