Calculate difference in dates with 12 hour format

[SaadSaleem]

I want to calculate the difference between two dates. e.g. 7/1/2020 2:30:00 AM and 7/8/2020 2:19:00 PM. These are in the AM/PM format therefore i think we will have to convert it. Using excel the answer is 7.4923. Using Alteryx, it gives me the error of fieldconversion: "Formula (18) DATETIMEDIFF1: "7/9/2020 7:31 PM" is not a valid Date or Time"

[JosephSerpis]

Hi @SaadSaleem you will need to convert the date to a format alteryx recognises as a date which is YYYY-MM-DD HH:MM:SS. I mocked up a workflow that does that and produces the same number as excel.

[SaadSaleem]

Hi Joseph, thanks for sharing this. However, i am still facing the same error. I have attached my workflow and the formula has been added to the far right. Can you try to run this at your end?

Thanks.

[JosephSerpis]

Hi @SaadSaleem your workflow has no data. You need to export your workflow via Options - Export Workflow.

[SaadSaleem]

Sorry, you can view it now.

[JosephSerpis · Accepted solution]

Hi @SaadSaleem try this syntax ((DateTimeDiff(DateTimeParse([Right_Edit Date],"%m/%d/%Y %I:%M %p"),DateTimeParse([Edit Date],"%m/%d/%Y %I:%M %p"),"minutes"))/60)/24 in the formula tool. When you datetimeparse or whenever you are changing your dates to a format alteryx recognizes it needs to match the format of your data. Which in your case is mm/dd/yyyy hh:mm pm so just needed to drop the syntax for seconds. 

[SaadSaleem]

Wow this works. Thanks Joseph, you're the best!!