Multiple date formats in one column

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

Datasets

Error Message

Regex

Date Time

Data Investigation

Accepted answers

Luke_C

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.

New Workflow9.yxmd

All comments

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-p/474994

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

binu_acs

@kevinvozza very similar to @Luke_C solution

DateTimeFormat_27_06.yxmd

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

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

dates.xlsx

Luke_C

Hi @kevinvozza

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

New Workflow9.yxmd

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

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!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335