How create a rule with dates?

Question

HI team

I created this formula but is not working because alteryx dont consider the year, someone know how can I use this formula ?

if [Fecha recepcion DIAN]<=[Fecha inicial periodo] then "Doc before current period" elseif [Fecha recepcion DIAN]>[Fecha final periodo] then "Doc after current periodo" else "OK" endif

apparently is considering only the month no the year. 2022 is before fecha final periodo but is showing as after.

I appreciate your support here

Fecha recepcion DIANFecha inicial periodoFecha final periodoDIAN report del periodo?   1/12/20238/5/20238/30/2023Doc before current period  1/16/20238/5/20238/30/2023Doc before current period  12/21/20228/5/20238/30/2023Doc after current periodo  12/9/20228/5/20238/30/2023Doc after current periodo  12/28/20228/5/20238/30/2023Doc after current periodoIT IS CORRECT 12/21/20228/5/20238/30/2023Doc after current periodoIT IS WRONG

DataNath · Accepted Answer

Hey @KarenHM - your 'dates' are currently in mm/dd/yyyy format which Alteryx can't work with. Alteryx can only handle ISO format (YYYY-MM-DD). We can add a DateTimeParse() function around the fields to parse them into the correct format in order to conduct the comparisons. Try the following expression and see how you get on:

if DateTimeParse([Fecha recepcion DIAN],'%m/%d/%Y')<=DateTimeParse([Fecha inicial periodo],'%m/%d/%Y') then "Doc before current period" elseif DateTimeParse([Fecha recepcion DIAN],'%m/%d/%Y')>DateTimeParse([Fecha final periodo],'%m/%d/%Y') then "Doc after current periodo" else "OK" endif

MarqueeCrew · Accepted Answer

@KarenHM ,

you need to use YYYY-MM-DD dates.

DateTimeParse([DateField],"%m/%d/%Y")

If you modify your formula and surround each date with that function, you're going to like the results.

cheers,

mark