Converting Inconsistent Formatting to a Date Field

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

Tips and Tricks

Parse

Date Time

Preparation

Accepted answers

nagakavyasri

@averyoldakowski One way of doing this:

Screenshot 2024-09-17 163155.png

9-17-24.6.yxmd

All comments

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

@averyoldakowski One way of doing this:

Screenshot 2024-09-17 163155.png

9-17-24.6.yxmd

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:

Screenshot 2024-09-18 111507.png

9-18-24.1.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703