multiple ElseIF statements

Question

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!

Tyro_abc · Answer

Please find my workflow attached

WeekEnding.yxmd

Tyro_abc · Answer

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

mot · Answer

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!