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.
Check out the latest post: All Models Are Wrong
The Excel number for a modern date is most easily calculated as the number of days since 12/30/1899 on the Gregorian calendar.
Attached please find a v10.0 workflow that converts a double (Excel date) to a Datetime in Alteryx.
It uses this function in a Formula Tool:
where Datebase = a datetime value set to "1899-12-30 00:00:00"
and Field1 is double value = 42397
Some people might be interested in why the process starts at 12/30/1899 instead of 12/31/1899 (which is really the last day of that year).
If you are, you can read the Microsoft explanation of the "bug" that is in Excel where it considers 1900 as a leap year and assumes that there is the date, 2/29/1900, in the calendar. Because of that, using the number within Excel needs the adjustment back an extra day...thus the reason for using 12/30/1899.
The MS article can be found here.