Date Time Difference fuction

[pangersandmash]

Hello

I'm struggling to calculate the difference between two dates. My excel input has the dates in 'General' format, presented dd/mm/yyy.

I have also tried setting excel input to store the dates as 'short date'.

I changed the date format as shown in the screenshot below, but none of the above seem to work...

Please could you help? 

[DanielG]

@pangersandmash - check out this page.  Extremely useful.  I reference it constantly.  https://help.alteryx.com/20231/designer/datetime-functions

DateTimeFormat only works on fields that are already dates.  In Parse Tools there is a DateTime Tool that will convert them if you don't want to deal with formulas to do it.

 

Try this if you want to do it within the formulas

 Use the parse formula to create new fields, with date/time date type.  %d/%M/%Y I believe is what it is, but the reference documentation will help nail it down.

[pangersandmash]

Thanks for reaching out. If you see in the screenshot i have attempted to use DateTimeFormat already...to no avail

[cjaneczko]

Your screenshot does not show the data type of Sub Start Date and Sub End Date. This makes a difference on how the formula is constructed. Can you share the date types of those two fields?

[DanielG]

@pangersandmash - that wont work on a String though.  Just because it looks like a date to us, doesnt mean Alteryx thinks its a date.  :) 

Look at this very simple example that I attached to show you how to convert a string that looks like DD/MM/YYYY to an Alteryx Date which is formatted as YYY-MM-DD

Checking your metadata in the results window will show you the data types to help with my point.

[Rana_Kareem]

Hi @pangersandmash ..

To change the date format from string to the standard date format (yyyy-mm-dd) you have to use DateTimeParse function, not DateTimeFormat.

If the date was in this format (dd/mm/yyyy)

You can change it using this expression:

DateTimeParse([Date Field], “%d/%m/%Y”)

You have two options: Using the above expression OR Using DateTime Tool as @DanielG pointed out.