String to Date conversion with Multiple formats

[toxicboy]

Hi

I am trying to convert date in string format to DATE format, but the row has multiple formats, Could I request assistance to convert all values with multiple formats to 1 single DATE format value?

Input data:

ID	Date (String format)	DATE (DATE format)
1	30-Sep-29	2029-09-30
2	23-MAR-30	2030-03-23
3	2025-06-23	2025-06-23

Appreciate your efforts

[DataNath · Accepted solution]

Hey @toxicboy, there's multiple ways to do this. Are these the only 2 formats you might have? If so, you can use the following in a Formula tool expression:

IF IsNull(ToDate([Date (String format)]))
THEN DateTimeParse([Date (String format)],'%d-%b-%y')
ELSE [Date (String format)]
ENDIF

[toxicboy]

Thanks @DataNath for your quick response as it works perfectly for now. Will this logic work even with more different formats if comes in. Risk is due to some manual files, each teams manage their own string format of date. By applying this logic, should it work for all different type of formats?

[Carolyn]

An issue that I have on my team is some people use /, some use -, and one person uses the . to denote separators. I don't know if you run into that issue, but I've handled that by starting with a Replace and making them all dashes. Then I run them through something similar to what @DataNath suggested, and this way I don't have to mess around with having a separate block of logic if it's a / and then almost the same logic if it's a -. Standardize first then parse into dates

Replace(Replace([Date], "/", "-"), ".", "-")

To your question above, you should be able to apply DataNash's logic to your different formats. Though there's only so much you can do if your people decide to do something bizarre like "30th Janur, Twenty twenty-five" or weird stuff like that. I encourage my users to standardize their formats or choose from common formats, vs just having it a free-for-all. Though I know you can't always do that

[alexnajm]

@toxicboy if there is a new case that comes in with a new format, you'll have to add it to the Formula

[toxicboy]

Thank you all for your assistance.

[Raj]

.