Calculate difference in dates with 12 hour format

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"

Text Mining

Accepted answers

JosephSerpis

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.

All comments

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.

Dates_030802020.yxmd

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.

Time to Process.yxmd

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.

Time to Process.yxzp

JosephSerpis

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167