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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How to Create Date from Excel Date Formatted as Number

Alteryx
Alteryx
Created on
 

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

 

convert_excel_date.JPG

Attachments
Comments
Alteryx Alumni (Retired)

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. Smiley Wink

The MS article can be found here.