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

[Luke_C]

Hi @kevinvozza 

Basically a big if statement. If you're not familiar with the datetime tools let me know and I can put an example together.

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

[Luke_C]

@kevinvozza 

I had some time, this formula should do the trick, just replace field1 with your field:

I get 6/18 for 44365 in both alteryx and excel

IF Length([Field1]) = 5
then todate(tonumber([Field1]))
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

[binuacs]

@kevinvozza very similar to @Luke_C  solution

[christine_assaad]

Hi @kevinvozza 

You may also find this macro useful

https://community.alteryx.com/t5/Engine-Works/The-Cleanse-Macro/ba-p/2363

Cheers!

[kevinvozza]

Hi @Luke_C thanks for you help. I am getting these errors when using the formula 

ConvError: Formula (2): DATETIMEPARSE: Cannot convert "2021-04-19 00:00:00" to a date/time with format "%d-%m-%Y" and language "English": Expected separator '-%m-%Y', got: '21-04-19 00:00:00'

ConvError: Formula (2): DATETIMEPARSE: Cannot convert "29-APR-2022" to a date/time with format "%d-%m-%Y" and language "English": Expected a number for Month: 'APR-2022'

[Luke_C]

@kevinvozza Can you please send the workflow with some sample data? Worked fine for the dates provided. 

Thanks!

[kevinvozza]

Hi - I have attached the sample data. I am using the exact workflow provided, most of the data formats correctly but not all 

[Luke_C · Accepted solution]

Hi @kevinvozza 

Looks like yyyy-mm-dd was another format not mentioned in the original post. The attached should handle this.

I noticed in your excel the formatting is very inconsistent within the column itself, some general, some custom, some date. For the dates, depending on your version, alteryx will automatically put them in the appropriate yyyy-mm-dd format. 

[Jayapriya]

Hi Team,

As I mentioned below Screenshot, I am expecting the mentioned formula is should not be applied for my 2nd date which I have highlighted in Yellow colour.But my formula is applying for my 2 nd date also with the wrong date format. The 2nd date I am expecting the format is yyyy-mm-dd which is 2023-03-08 but its wrongly converted like 2023-03-08.Kindly help me to resolve this issue.

Thanks,

JayaPriya M