community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Convert excel numeric date to date format

Atom

Hi,

 

My file has a column that includes both date format (yyyy-mm-dd) and excel numeric date. Is there a way to convert both of them to the date format mm/dd/yyyy in Alteryx?

 

Thanks,

Alteryx Certified Partner
Alteryx Certified Partner

Hi @hailey19 

 

To convert numeric dates in the date format of Alteryx, you should use this formula:

 

DateTimeAdd('1900-01-01', [NumericDate]-2, "days")

 

This is because the numeric value of a date starts on January 1st, 1900, so this is date 1. Adding your numeric value - 2 to this date will get your actual date.

 

Cheers,

Highlighted
Aurora

Hi @hailey19 

 

One of the little know Alteryx facts is that the ToDate() and ToDateTime() function will automatically convert dates using the excel formula that @Thableaus recreated if the argument is a number

Formula.png

 

 

date.png

 

This table also explains the 2 day offset that @Thableaus uses in his formula.  Day 0 is actually 1899-12-30. 

 

Dan 

Alteryx Certified Partner
Alteryx Certified Partner

Awesome @danilang 

 

I heard something about Excel considering 29th February, 1900 an existing date, which is not.

So that was the reason for an offset of 2.

 

Cheers,

Labels