Date conversion from excel

[aysha6029]

Hi Fiends,

I extract an excel sheet from an application on the below format. i need to convert the Date02 field to understandable format(ISO or USA Date format) from excel using alteryx. i request your help to help me solve this issue

S.No	Name	Date01	Date02
1	Holly	2009-09-30	40757
2	Peter	2009-08-25	40940
3	Sanson	2009-07-17	41306

[MarqueeCrew]

Excel dates are days after 1900-01-01.  Given that, I would use a formula for a new DATE field like:

DateTimeAdd("1900-01-01",[Date02],"days")

This should work for your conversion as I understand dates.

Thanks,

Mark

[aysha6029]

i tried the below formula. i get an error message saying " Argument 2 of DATETIMEADD is not an integer. Please help me

[MarqueeCrew]

@aysha6029,

The data type for your field, Date02, is a string (i'm guessing).  You can use a select and change the type from "string or vstring" to Int32.  The formula will then see it as an integer and all will be well.  But if the field value contains null values and or non-numeric data, you might have problems.

You could also modify the code to:

DateTimeAdd("1900-01-01",tonumber([Date02]),"days")

Try this and see if it works for you.  

[aysha6029 · Accepted solution]

Yes, it contains nulls as well.. how to handle this sir.

[MarqueeCrew · Accepted solution]

did you try the updated formula?

[aysha6029]

yes.. i tried the updated formula. the null value columns are filled with "1900-01-01" dates and the rest of the converted columns are converted to the date format but it is also adding two extra days to the existing record when i compared with my original excel file 

[JordanB · Accepted solution]

hi @aysha6029

I have attached a workflow which looks at all excels serials. The one you want to use in the far right hand side flow. 

Let me know if this works. 

Best,

Jordan Barker

Solutions Consultant. 

[aysha6029]

Hi JordanB,

Thank You. i got one issue solved. ie the excel values like 42097 is getting converted to proper date. I have one more issue here.. there are some null values in the record. when i try to use this formula to all the rows in that field, the null column is getting populated with 1900-01-01. Please help me to solve this issue.

[MarqueeCrew · Accepted solution]

What resolution would you like?

If the nulls get converted to 1900-01-01 and you would like them back as NULLS, then you can use:

IIF([Date02]=='1900-01-01',Null(),[Date02])

Does that work for you?