Data from Excel contains both date format and number

Question

Hi,

The data in the EXCEL is like:

Maturity231905-04-143501906-09-19

when I import the data into Alteryx, it reads as string. How can I convert the numbers 23 and 350 to dates 01/23/1900 and 12/15/1900 and reformat the date 1905-04-14 and 1906-09-19 to 04/14/1905 and 09/19/1906? I tried the formula ToDate, or to change the data type in Select, neither worked.

Yoshiro_Fujimori · Accepted Answer

Hi @fullmoon558 ,

Please try this expression in Formula tool.

Date =

IF Contains([Maturity], "-")
THEN [Maturity]
ELSE ToDate(ToNumber([Maturity]))
ENDIF

Output

BTW, you may find the date does not match with that of Excel.

The Date conversion of Excel has an issue around 1900-02-28 and 1900-03-01.

For detail, please check my favorite post here.

https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/

Yoshiro_Fujimori · Accepted Answer

I forgot the "reformat" part in your requirement.

Date2 = DateTimeFormat([Date],"%m/%d/%Y")

Output