IF Function Dates

[DHB]

I've written this IF statement which isn't working properly i.e. every non-null Due_At date comes out as 'Swot Week' or and every null Due_At date comes out as 'invalid'.  Most of the non-null Due_At dates should be in the other categories.

if [Due_At_Date]<'24/06/2019' and [Due_At_Date]>'16/06/2019' then 'Swot Week'

elseif [Due_At_Date]>'09/06/2019' then '13'

elseif [Due_At_Date]>'02/06/2019' then '12'

elseif [Due_At_Date]>'26/05/2019' then '11'

elseif [Due_At_Date]>'19/05/2019' then '10'

elseif [Due_At_Date]>'12/05/2019' then '9'

elseif [Due_At_Date]>'05/05/2019' then '8'

elseif [Due_At_Date]>'28/04/2019' then '7'

elseif [Due_At_Date]>'21/04/2019' then 'MSB Week 2'

elseif [Due_At_Date]>'14/04/2019' then 'MSB Week 1'

elseif [Due_At_Date]>'07/04/2019' then '6' elseif [Due_At_Date]>'31/03/2019' then '5'

elseif [Due_At_Date]>'24/03/2019' then '4' elseif [Due_At_Date]>'17/03/2019' then '3'

elseif [Due_At_Date]>'10/03/2019' then '2' elseif [Due_At_Date]>'03/03/2019' then '1'

else 'invalid' endif

Can anyone see what I'm doing wrong?

Thanks in advance for your help.

[BenMoss]

Hey!

Your fate strings need to be formatted as yyyy-mm-dd

Ben

[AndrewBanh]

Hi @DHB 

Try converting the dates you have into UTC format instead. Use the DateTime tool and select "dd/MM/yyyy" to convert your dates.

Then change the dates in your formula so they have the formate of yyyy-MM-dd (which is also UTC format).

Let me know if this helps. 

- Andrew

[DHB · Accepted solution]

I've got it.  Thank you both for your help...

if [Due_At_Date] >= "2019-06-17" AND [Due_At_Date] <= "2019-06-23" then 'Swot Week'
elseif [Due_At_Date] >= "2019-06-10" AND [Due_At_Date] <= "2019-06-16" then '13'
elseif [Due_At_Date] >= "2019-06-03" AND [Due_At_Date] <= "2019-06-09" then '12'
elseif [Due_At_Date] >= "2019-05-27" AND [Due_At_Date] <= "2019-06-02" then '11'
elseif [Due_At_Date] >= "2019-05-20" AND [Due_At_Date] <= "2019-05-26" then '10'
elseif [Due_At_Date] >= "2019-05-13" AND [Due_At_Date] <= "2019-05-19" then '9'
elseif [Due_At_Date] >= "2019-05-06" AND [Due_At_Date] <= "2019-05-12" then '8'
elseif [Due_At_Date] >= "2019-04-29" AND [Due_At_Date] <= "2019-05-05" then '7'
elseif [Due_At_Date] >= "2019-04-22" AND [Due_At_Date] <= "2019-04-28" then 'MSB Week 2'
elseif [Due_At_Date]>= "2019-04-15" AND [Due_At_Date] <= "2019-04-21" then 'MSB Week 1'
elseif [Due_At_Date] >= "2019-04-08" AND [Due_At_Date] <= "2019-04-14" then '6'
elseif [Due_At_Date] >= "2019-04-01" AND [Due_At_Date] <= "2019-04-07" then '5'
elseif [Due_At_Date] >= "2019-03-25" AND [Due_At_Date] <= "2019-03-31" then '4'
elseif [Due_At_Date] >= "2019-03-18" AND [Due_At_Date] <= "2019-03-24" then '3'
elseif [Due_At_Date] >= "2019-03-11" AND [Due_At_Date] <= "2019-03-17" then '2'
elseif [Due_At_Date] >= "2019-03-04" AND [Due_At_Date] <= "2019-03-10" then '1'
else 'invalid' endif