Formula for Time

[Dav]

Hi All

I am trying to create a formula which will tell me if a date is between two other dates. I have written the following formula down, but when I check the result, I can see it has worked

One of my entries is dated 01/01/2025  the start date is 12/12/2024 and the end date is 30/01/2025. The result came back as no, which is wrong. I even wrote a formula in excel, and that came back as yes. 

All the data points are down as date and size 10. Can anyone help

IF [Accounting Cycle Date] >= [Start Date] AND [Accounting Cycle Date] <= [End Date] THEN "Yes" ELSE "No" ENDIF

[binuacs]

@Dav, you need to convert the date into Alteryx date format (yyyy-mm-dd)  using DateTimeParse() function

IF DateTimeParse([Accounting Cycle Date],'%d/%m/%Y') >= DateTimeParse([Start Date],'%d/%m/%Y') AND DateTimePArse([Accounting Cycle Date],'%d/%m/%Y') <= DateTimeParse([End Date],'%d/%m/%Y') THEN "Yes" ELSE "No" ENDIF

[Dav]

Thank you for your help, whilst running it came up with this warning. I can confirm all the dates are in excel under DD/MM/YYY. The formula did not work as everything is coming back as yes

[binuacs]

@Dav , some of your dates are in the format of YYYY-MM-DD eg: 2024-12-20 from the warning, that means you have mixed format in your input data, the above formula only works if the date is in the format of dd/mm/yyyy, if your input file has different date format then you need to identify them then convert it into alteryx date format, may be if you can provide some sample data i can work on that

[Dav]

Thankyou @binuac for the support. I will investigate the data.