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') ='18:00' && DateTimeFormat([Time],'%H%M') ='06:00' && DateTimeFormat([Time],'%H%M') ='18:00' && DateTimeFormat([Time],'%H%M') ='06:00' && DateTimeFormat([Time],'%H%M') ='18:00' && DateTimeFormat([Time],'%H%M') ='06:00' && DateTimeFormat([Time],'%H%M') ='18:00' && DateTimeFormat([Time],'%H%M') ='06:00' && DateTimeFormat([Time],'%H%M') ='18:00' && DateTimeFormat([Time],'%H%M') ='06:00' && DateTimeFormat([Time],'%H%M') ='18:00' && DateTimeFormat([Time],'%H%M') ='06:00' && DateTimeFormat([Time],'%H%M') ='18:00' && DateTimeFormat([Time],'%H%M')<='05:59') then 'C-Crew' else 0 end Any help would be appreciated

Defining a work crew by dayname and time

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

Expression

Accepted answers

Mfuson

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

All comments

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.

1208-Mfuson.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335