Defining a work crew by dayname and time

[Mfuson]

Hello, i'm new to Alteryx and i'm trying to add a formula to calculate which work crew by using Daynumber and time.  Here is what i've tried but can't seem to get it to work.

 if([DayNumber]='1' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'A-Crew' elseif
if([DayNumber]='1' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'C-Crew' elseif
if([DayNumber]='2' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'A-Crew' elseif
if([DayNumber]='2' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'B-Crew' elseif
if([DayNumber]='3' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'A-Crew' elseif
if([DayNumber]='3' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'B-Crew' elseif
if([DayNumber]='4' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'A-Crew' elseif
if([DayNumber]='4' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'B-Crew' elseif
if([DayNumber]='5' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'C-Crew' elseif
if([DayNumber]='5' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'B-Crew' elseif
if([DayNumber]='6' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'C-Crew' elseif
if([DayNumber]='6' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'SS-Crew' elseif
if([DayNumber]='0' && DateTimeFormat([Time],'%H%M')>='06:00' && DateTimeFormat([Time],'%H%M')<='17:59') then 'SS-Crew' elseif
if([DayNumber]='0' && DateTimeFormat([Time],'%H%M')>='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'C-Crew'
else 0
end

Any help would be appreciated

[christine_assaad]

Hi @Mfuson 

Couple of notes here that could help:

1- After elseif, you don't need to put another "if". It should be if xxxxxx then xxxx elseif [DayNumer]xxxxx..etc

2- The datetimeformat you're using is %H%M which will give you 600 (looking at first condition) instead of the 6:00 you specified (%H:%M).

3- You have the time as string or text and in this case, operators such as >= are not going to give you what you are looking for.

Hope this helps. Cheers!

[Qiu]

@Mfuson 
I think the situation is a bit more complicated than a formula tool.

We have also consider the night shift, which slides to next day.

[Mfuson · Accepted solution]

Thanks for everyones input.  After alot of trial and error, i was able to adapt "QiU's" solution to my workflow.  Thanks QIU.