Multiple Date Format in One Column

[christinachau22]

Hi all,

I am new to Designer and was wondering if someone could provide assistance. I have a v_string column with two formats and I want the results to be in "mm-dd-yyyy" format.

Date

2023-04-14

05/12/2023 11:00 EDT

01/12/2023 10:22 EDT

2023-02-15

[rzdodson · Accepted solution]

@christinachau22: this will work for string fields in a Formula tool:

If REGEX_Match([Date], "\d{4}-\d{2}-\d{2}") 
then Substring([Date],5,2)+"/"+Substring([Date],8,2)+"/"+Left([Date], 4)
else Left([Date], 10)
endif

The regex portion tests whether the strings follow a certain pattern and then reformats the string based on the date format you are wanting. If it matches, then we rebuild your date format to how the other two look. If it does not match, then we return the left 10 characters of your string field.

[christinachau22]

Thanks so much! I got it to work! I'm not familiar with substrings. Can you explain what the 5,2 and 8,2 is used for? I'm not able to find much on the function.

Substring([Date],5,2)+"/"+Substring([Date],8,2)+"/"+Left([Date], 4)

[aatalai]

@christinachau22 take a look at this challenge

https://community.alteryx.com/t5/Weekly-Challenges/Challenge-414-Mastering-Date-Formats/m-p/1250068#...

[rzdodson]

@christinachau22 the Substring function basically allows you to search within a string field. It's Excel equivalent would be the MID function, if you are familiar with that. The 5 and 8 in this use case tell the Substring function when to start retrieving data. That is, I want to start retrieving data after I hit the fifth and eight character. Then, the 2s are me telling the function how many characters to grab.

If the solution above helped you out, please go ahead and mark the post as an Accepted Solution so that others can find it in their future research. :)

[christinachau22]

Thank you so much! This is helpful. Appreciate the explanation.

[binuacs]

@christinachau22 using datetime functions