multiple ElseIF statements

[slack6]

I have 4 years worth of fiscal year timecard data. I want to separate by fiscal year first before running other analytics. I have written this ElseIf statement, but the data is only being bucketed into FY18 or FY21 (there is data for FY19 and FY20). 

if [Week Ending] < "9/30/2018" then "FY18"

elseif [Week Ending] <= "9/30/2019" then "FY19"
elseif [Week Ending] <= "9/28/2020" then "FY20"
else "FY21"
endif

Thanks in advance! 

[mot]

Hi @slack6,

It looks like your dates are in string format.  You can use datetime tool first to transform the dates from strings to datetime. Once you do that, the formula would work as intended. 

I hope this helps. Best!

[Tyro_abc]

Hi 

Can you try this formula? You can also break it in parts for better readability 

if DateTimeParse([Week Ending],'%m/%d/%Y') < DateTimeParse("9/30/2018",'%m/%d/%Y') then "FY18"

elseif DateTimeParse([Week Ending],'%m/%d/%Y') <= DateTimeParse("9/30/2019",'%m/%d/%Y') then "FY19"
elseif DateTimeParse([Week Ending],'%m/%d/%Y') <= DateTimeParse("9/28/2020",'%m/%d/%Y') then "FY20"
else "FY21"
endif

Best Regards

Arundhuti

[Tyro_abc]

Please find my workflow attached