Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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
16 - Nebula

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
16 - Nebula

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
16 - Nebula

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

DateTimeAdd("1900-01-01",toNumber([Dates])-2,"days")
DataNath
14 - Magnetar

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
16 - Nebula

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