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.

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 some are not recognized as dates. Any help is appreciated, example data is attached 

 

44358 converts to 6/11/2021 in excel 

6 REPLIES 6
IraWatt
17 - Castor
17 - Castor

Hi @kevinvozza,

What date format is "44358" how is it structured?

Thanks,

Ira

kevinvozza
6 - Meteoroid

It converts to 6/11/2021 in excel 

IraWatt
17 - Castor
17 - Castor

Ah thanks I think it is Msdate date. This workflow converts all the rows to dates:

IraWatt_0-1656441740275.png

Any questions or issues please ask :)
HTH!
Ira

 

IraWatt
17 - Castor
17 - Castor

@kevinvozza just updated the formula for Msdate dates I think it should be 

DateTimeAdd("1900-01-01",toNumber([Dates])-2,"days")
DataNath
17 - Castor

One way of doing this with a Formula tool:

 

IF REGEX_Match([Dates], '\d+') then todate(ToNumber([Dates])) else DateTimeParse([Dates],'%Y-%m-%d') endif

 

DataNath_0-1656442119100.png

 

Luke_C
17 - Castor

Is this the same issue that you posted yesterday? A lot of good options provided there.

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multiple-date-formats-in-one-column/m-...

 

Labels