Input data with multiple format date in one column

[NurulAtikah]

Hi everyone,

I have a question regarding input data and data type, currently, I have one column name "Date" in excel and it consists of 2 formats of data one in date and one in-text format. When I input the excel file in alteryx, alteryx detect as Date format, and date in text format become null. Is there any possible way to make the value date appear (not null)?

Thank you

[atcodedog05]

Hi @NurulAtikah 

Here is a formula for the task.

IF !IsEmpty(DateTimeParse([Date],"%d/%m/%y")) 
THEN DateTimeParse([Date],"%d/%m/%y") 
ELSEIF !IsEmpty(DateTimeParse([Date],"%d-%b-%y"))  
THEN DateTimeParse([Date],"%d-%b-%y") 
ELSE Null() ENDIF

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[NurulAtikah]

Hi @atcodedog05 ,

Thank you for your response.

However, the problem is when I input data using the input tool, the date which in the format string (20-Feb-2020) becomes null so I can't convert as your suggestions.

Thank you.

[atcodedog05]

Hi @NurulAtikah 

Can you provide a sample file.

[grazitti_sapna]

Hi @NurulAtikah . can you share your input file, so that I can test it? One thing I can think of to solve this issue is to convert your date field into string using select tool.

I believe you will be able to read all the data then after that you can apply formula tool and convert the dates into alteryx format and change the data type into date by copying the values into another column or can replace the datatype of the same column using select tool. I hope this will help you.

Thanks.

[NurulAtikah]

My file in format .xlsb however, I can share with you a sample in .xlsx format and you may change to .xlsb format because the one that has the problem is in .xlsb format.

My data in excel

And after input using input data, it becomes null

[atcodedog05]

Hi @NurulAtikah 

Sorry can you please share the .xlsb file format. I having some issues converting.

I will be able to help you much better you share that file.

There was no issue reading .xlsx file

[atcodedog05]

Hi @NurulAtikah 

I was able to replicate your issue on .xlsb file.

Output:

Seems like .xlsb file is sending meta data of columns datatype hence its running into a issue.

Let me look for a work around.

[atcodedog05 · Accepted solution]

Hi @NurulAtikah 

Kudos i was able to find a workaround.

While reading the file from input tool set first row has data like below Input tool config. Then it will take column as a string.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[grazitti_sapna]

Hi @NurulAtikah , my approach to the problem is also somehow similar to @atcodedog05  as I could not find any other possible way to tackle this.

Might be a slight difference.

Thanks.