Convert Mixed Date format to one format: UK (DDMMYYYY) & US (MMDDYYYY)

Hoping for some help as a really new to Alteryx Designer.

I have a customer spreadsheet which I need to work on in AD but dates are both in UK and US format ie DDMMYY and MMDDYY respectively. The data variations are in one column at present.

See image.

What I would like to be able to do is to be able to convert the whole column to one standard UK date ie DDMMYY .

Your guidance would be appreciated

Regex

Parse

Date Time

Best Practices

Accepted answers

DataNath

No problem @PamG - always happy to help! If this fully answers your question then feel free to mark the solution as this helps others with the same/similar issues find the topic easier in future. If you still need any further assistance then please do shout up!

All comments

DataNath

@PamG in terms of actually parsing the dates, this is fairly straight forward and we can use something like the following expression to check if numbers are outside of a certain range (i.e. 1-12 for months) and then apply the alternative treatment if so, before formatting the result into your desired UK format:

However, my worry here is that there's no real way of distinguishing dates that could either be dd/mm/yyyy or mm/dd/yyyy i.e. 04/05/2022 - how do we get Alteryx to differentiate whether the 4 and 5 are month vs day? This isn't an issue when we have 31/01/2022 as that has to be dd/mm/yyyy. Is there any other field in your data that indicates the country or something that we could perhaps build into the logic when telling AYX how to handle the date?

Parse US and UK dates.yxmd

PamG

@datanath; sadly the file does not indicate a Country. Just a mixture of UK & what looks like US dates.

Also from my image above the UK date doesn't include minutes/seconds where as the US format has m/d/yyyy h:mm:ss AM/PM so the expression would to cater for too before I proceeding to the next flow.

Even when I changed around your expression it didn't work for me - sorry I may not be doing that correctly.

Your example is great but didn't work successfully against my dataset

DataNath

My apologies @PamG - trimmed off the time element as you mentioned it was just MMDDYYYY. However, the fact that this is in the US time and not the UK is great as we can use this to differentiate, especially in scenarios I mentioned earlier. How does this new amended expression look? Have added in an extra example similar to the 'problem scenario' I talked about above to show it working:

We're basically telling Alteryx here - if the date appears in X format i.e. with the time element, parse it as MMDDYYYY, otherwise parse it as DDMMYYYY. From the result of that (which comes out in ISO format that Alteryx works with; YYYY-MM-DD), we then just format that into your desired UK format of DD/MM/YYYY.

Parse US and UK dates.yxmd

PamG

Hiya @DataNath

Thanks so much for your help on this. It worked perfectly and will use going forward.

Much appreciate your hep.

DataNath

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703