Determine date time format and convert to consistent output

[CAdatawrangler]

Hi all, I'm provided with a report where - depending on which country it is run in (eg. Australia vs US) - the date for the same column (eg. invoice date) could be in format yyyy/dd/mm or yyyy/mm/dd. Anyone know how we can build an Alteryx workflow that reads the data (which is for a full month with all days covered) and determines what format the dates are in (whether day then month, or month then day) and then outputs to mm/dd/yyyy? The workflow needs to handle both date formats rather than user needing to determine which format the input date is in.

Thanks in advance

[grossal]

Hi @CAdatawrangler,

I got something for you! Probably not the smartest way, but it's working.

Example 1:

Example 2:

It won't work if you mix both date styles in one input, but it will work properly if only one date style is present. Let me know what you think and please like it and mark it as a solution if it solved your problem. Workflow attached.

Best

Alex

[Maskell_Rascal]

Hi @CAdatawrangler 

See if the attached workflow will work for you. I'm creating a month column and filtering out anything that is correctly formatted as Alteryx requires for dates (yyyy-MM-dd). The F output is then used to convert the everything from a yyyy-dd-MM to yyyy-MM-dd. After combining the two data sets back together, we can then use another DateTime tool to convert the cleansed dates to your desired output. 

Let me know if this works for you. 

Thanks!

Mascal_Rascal

[CAdatawrangler]

Thanks @grossal! I think that works as we will never have a mix of both date formats in the same file. But the input could spread multiple months (eg. also include 2 Feb in the sample data). I tested it by adding this as an addition row in the input data, and the new date for some reason defaults to January rather than 2 Feb.  Any tips on fixing that one remaining issue?

[Maskell_Rascal]

Hey @CAdatawrangler 

The second formula tool needs to be updated to prevent the error you are getting. It should be 

DateTimeParse(([1]+[Month]+[Day]), '%Y%m%d')

I've updated @grossal workflow with the new formula for you to check out. 

Thanks!

Mascal_Rascal

[grossal · Accepted solution]

Oh sorry, I had a typo in the DateTimeParse. It should have been:

DateTimeParse([1]+[Month]+[Day],'%Y%m%d')

I have also added some additional training data to test for it. Should work now. See here:

😃

Best

Alex 

[CAdatawrangler]

Thankyou! That works