Converting Inconsistent Formatting to a Date Field

[averyoldakowski]

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, 2024	08/08/2024
8/14/2024	08/14/2024
Jul 15, 2024	07/15/2024
June 3, 2024	06/03/2024
15/08/2024	08/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

[OTrieger]

@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 · Accepted solution]

@averyoldakowski One way of doing this:

[OTrieger]

@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

[OTrieger]

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]

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

[averyoldakowski]

Yes, that is PERFECT!! Thank you!!

[averyoldakowski]

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]

@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/2024	07/2024
July-2024	07/2024
Jul-2024	07/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]

@averyoldakowski you can try this: