How create a rule with dates?

[KarenHM]

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 DIAN	Fecha inicial periodo	Fecha final periodo	DIAN report del periodo?
1/12/2023	8/5/2023	8/30/2023	Doc before current period
1/16/2023	8/5/2023	8/30/2023	Doc before current period
12/21/2022	8/5/2023	8/30/2023	Doc after current periodo
12/9/2022	8/5/2023	8/30/2023	Doc after current periodo
12/28/2022	8/5/2023	8/30/2023	Doc after current periodo	IT IS CORRECT
12/21/2022	8/5/2023	8/30/2023	Doc after current periodo	IT IS WRONG

[DataNath · Accepted solution]

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 solution]

@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

[KarenHM]

thanks you very much! now it is working :)