In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate difference in dates with 12 hour format

SaadSaleem
6 - Meteoroid

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"

6 REPLIES 6
JosephSerpis
17 - Castor
17 - Castor

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
6 - Meteoroid

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
17 - Castor
17 - Castor

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

SaadSaleem
6 - Meteoroid

Sorry, you can view it now.

JosephSerpis
17 - Castor
17 - Castor

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
6 - Meteoroid

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

Labels
Top Solution Authors