Regex Date Formatting

[cjohnston]

Hi, 

I am trying to cleanse dates that are held as MM DD YYYY format and also in MMMM YYYY, and am trying to use regex to convert a string such as March 2022 to the last day in march, 31/03/2022. Any advice on how to write a formula statement that would do this. It would need doing for all 12 months of the year so if theres a quick way to write that out, even better! 

Thanks

[atcodedog05 · Accepted solution]

Hi @cjohnston 

Here is how you can do it.

Workflow:

Hope this helps : )

[cjohnston]

Thanks so helpful!! I found a couple of other funky dates in the dataset which I have resolved with now that this is cleansed and have been working to try format them too, EG.  Oct 15th 2021 and Sept 2021. 

[atcodedog05]

Happy to help : ) and all the best with the date 🙂👍@cjohnston 

Cheers and have a nice day!

[cjohnston]

Thanks - if you could help with the "Oct 15th 2021" format too please that would be very appreciated! 

[atcodedog05 · Accepted solution]

Hi @cjohnston 

Try this

Hope this helps : )

[atcodedog05]

Happy to help : ) @cjohnston 

Cheers and have a nice day!

[cjohnston]

Thanks, you too :)