Determine date time format and convert to consistent output

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

Date Test.xlsx

Date Time

Accepted answers

grossal

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

2020-04-20-dynamic date conversion-updated.yxmd

All comments

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

2020-04-20-dynamic date conversion.yxmd

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

DateTime convert dynamic Australian and USA.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703