Free Trial

General Discussions

Discuss any topics that are not product-specific here.

Formula for Time

Dav
7 - Meteor

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

4 REPLIES 4
binuacs
21 - Polaris

@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
7 - Meteor

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



Screenshot 2025-01-23 155238.png

 

 

binuacs
21 - Polaris

@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
7 - Meteor

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

Labels