Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting Inconsistent Formatting to a Date Field

averyoldakowski
7 - Meteor

Hi all,

 

I have a string field that I would like to turn into consistent date values but all of the formatting is different: 

 

What I Have:What I Want:
Aug 8, 202408/08/2024
8/14/202408/14/2024
Jul 15, 202407/15/2024
June 3, 202406/03/2024
15/08/202408/15/2024

 

I think the last one in the table may be more difficult if it were, for example, 07/08/2024 (meant to be August 7th) but if the rest of the formats were fixed I could live with the difficulty of regional formatting differences.

 

I have tried to use regex but I can't quite figure it out and I would like to avoid "IF Contains([What I Have:], Jun) THEN '06'..." if at all possible. 

 

Thanks!

Avery

 

9 REPLIES 9
OTrieger
12 - Quasar

@averyoldakowski 
Just from looking at one point from all the data will make it only a guess.

Maybe there is a solution for it and maybe there is not, but just form looking at the list of data will not be enough information to make a proper decision. Sharing with us the full set of data, not necessary with all the rows but with all the columns will help analyze it. Additional data might give some light what is the source of the data and then that might shade some light on it's format.

nagakavyasri
12 - Quasar

@averyoldakowski One way of doing this:

 

Screenshot 2024-09-17 163155.png

OTrieger
12 - Quasar

@nagakavyasri 
Your solution will not hold for any dates that are 12/12 and smaller and the automation will not know if the first figure is a day or a month

nn.PNG

OTrieger
12 - Quasar

hat is the reason that I'm saying just based on the dates you cannot build a 100% solution for this issue, there need to be additional factor to determine what is the date format to be use. As long as the day count is bigger than 12 then no issue, but what with all the one that are smaller than 13? Need additional factor that might be got from the rest of the data, such as the original, if transactions, then based on the country you can figure out etc...

OTrieger
12 - Quasar

We need to whole set of data to determine if that is a possibility or not

averyoldakowski
7 - Meteor

Yes, that is PERFECT!! Thank you!!

averyoldakowski
7 - Meteor

We are only running this flow from month to month so we have a pretty good idea what the month should be so I don't think it's too big of a deal. If we were running this for a full year there would be more of an issue for sure!

averyoldakowski
7 - Meteor

@nagakavyasri @OTrieger Is there a way this could be altered to work for just month and year? I have another column formatted as follows:

 

What I Have (Review Period):

What I Want:

7/2024

07/2024
07/202407/2024
July-202407/2024
Jul-202407/2024

 

This prevents the 08/09 vs 09/08 problem and could be used to create a formula to fix that issue (date of review from the previous problem should be in the month after this review period).

 

Thank you!

Avery

nagakavyasri
12 - Quasar

@averyoldakowski you can try this:

 

Screenshot 2024-09-18 111507.png

Labels