on 01-04-2017 10:41 AM - edited on 05-25-2023 08:49 AM by lolcott
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:
DateTimeAdd([Datebase],[Field1],"days")
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.