Different date format in alteryx

Question

Hi All,

I have 2 excel with two similar format but when i input them in Alteryx as an input source one of them churn out a different format. ie yyyy/mm/dd

2 questions:

Can i make standardize them in alteryx so they either turns as yyyy/mm/dd or mm/dd/yyyy

Is there any i can run this dynamic as these file comes in daily and without having me to use Datetime tool?

Thank you so much

Excel

Alteryx

afv2688 · Accepted Answer

Hello @KLS,

Alteryx only has one format to show date time which is yyyy-mm-dd HH:MM:SS. The rest are only v_strings. If you want to standarize a way to input the dates you would need first to know the format they come in and transform them into something you could use.

I would recommend you to input your data, transform it into date-time within alteryx, operate with it and at last if you would like then give to it the format you like.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

danilang · Accepted Answer

Hi @KLS

When Alteryx reads from an Excel file it tries to interpret the data type by looking at the data and the cell formatting information.

In the attached excel file, the first column is formatted as DateTime and the second is formatted as Text.  Even though it looks the same in excel, Alteryx reads them differently.  The first column is interpreted as a Date and converted to Alteryx DateTime format.  The Second is read as a string.

You can deal with differences by using a conditional formula based on the presence of "/" like

If contains(tostring([_CurrentField_]),"/") then 
	DateTimeParse(tostring([_CurrentField_]),"%m/%d/%Y %H:%M:%S")
Else
	[_CurrentField_]
endif

Here, I'm using a Multi Field formula to convert both columns at once, giving two new DateTime fields

Dan