Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple date formats in one column

kevinvozza
6 - Meteoroid

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

15 REPLIES 15
Jayapriya
6 - Meteoroid

Can any one pls help me on above mentioned issue?

Luke_C
17 - Castor

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
6 - Meteoroid

Thanks Luke will try the above mentioned method.

 

Thanks,

JayaPriya M

freemajb
5 - Atom

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
17 - Castor

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
5 - Atom

That worked, thank you!

Labels