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
Solved! Go to Solution.
@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?
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.
