Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data from Excel contains both date format and number

fullmoon558
6 - Meteoroid

Hi,

 

The data in the EXCEL is like:

Maturity
23
1905-04-14
350
1906-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. 

2 REPLIES 2
Yoshiro_Fujimori
15 - Aurora

Hi @fullmoon558 ,

 

Please try this expression in Formula tool.

Date = 

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

 

Output

Yoshiro_Fujimori_0-1683775200405.png

 

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
15 - Aurora

I forgot the "reformat" part in your requirement.

 

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

 

Output

Yoshiro_Fujimori_0-1683775832340.png

 

Labels
Top Solution Authors