This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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.
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
This table also explains the 2 day offset that @Thableaus uses in his formula. Day 0 is actually 1899-12-30.
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.