Date Formatting- Convert to same format, error if format incorrect

[aish28warya]

Hi all,

I've an input data where dates are usually in the below two formats:

2021.05

03/2020

I need a condition where I'm converting the dates in MM/YYYY.

something like

if 2021.05 then 05/2021 else 05/2021.

And a way where if the Date formats turn out to be anything other than the above two then either change them to MM/YYYY(though I tried building something like this but didn't work) or ultimately provide an error so that the output is not published with an incorrect date format.

[atcodedog05]

Hi @aish28warya 

You would need to have list of all date formats that exist in your data to handle dates properly.

[danilang · Accepted solution]

hi @aish28warya 

You can do this in a Formula Tool

if !regex_match([Date],"\d{4}\.\d{2}") and !regex_match([Date],"\d{2}\/\d{4}") then
	"error"
elseif !contains([Date],"/") then
	substring([Date],5,2)+"/"+left([Date],4)
else
	[date]
endif

The first clause returns "error" if it's not one of the 2 formats.  The second one converts it to mm/yyyy if its yyyy.mm.  The third one returns the original date since the only remaining case is mm/yyyy

If you have more known formats, then you can expand this statement to include and convert them as well. 

Dan