Multiple date formats in one column

[kevinvozza]

Hi all, 

I have one column that contains multiple date formats. I would like to format them all as yyyy-mm-dd but unsure how to convert so many formats. Any help is appreciated. 

Date Formats in Column: 

-  44365 (this format represents 6/11/2021)

-  5/14/2021 0:00

-  3-Oct-21

-  23-07-2021 00:00

[Jayapriya]

Can any one pls help me on above mentioned issue?

[Luke_C]

Hi @Jayapriya 

Switch around the %d and %m in the formula. Those dictate which part of the data is read as a day/month, respectively. I'd also suggest making a new post if that doesn't solve it - that will get addressed quicker by the community. 

[Jayapriya]

Thanks Luke will try the above mentioned method.

Thanks,

JayaPriya M

[freemajb]

Hello,

Does anyone know how I would adjust/add to the IF statement from Luke above if one of my date formats is 02/2025, missing the day? Is there a way for Alteryx to add say "01" as the day to the dates missing the day? Thanks for you help!

[Luke_C]

Hi @freemajb 

Try this. For your format the relevant syntax is datetimeparse([Field1],'%m/%Y'). That will automatically put 1 as the day.

IF Length([Field1]) = 5
then todate(tonumber([Field1]))
elseif regex_match([Field1],'\d{2}\/\d{4}')
then datetimeparse([Field1],'%m/%Y')
elseif contains([Field1],'/')
then datetimeparse([Field1],'%m/%d/%Y')
elseif REGEX_Match([Field1],'\d+-.*-\d{2}')
then datetimeparse([Field1],'%d/%b/%y')
else datetimeparse([Field1],'%d-%m-%Y')
endif

[freemajb]

That worked, thank you!