Excel stores a date as the number of days since the start of 1900, and only formats it as a date on the sheet. When a workbook or a CSV export reaches QuickSight with the format lost, the date column shows numbers like 46157. The number is still the date; it only needs converting.
Copy and paste
Whole days:
addDateTime({Excel Date} - 2, 'DD', parseDate('1900-01-01'))
With the time of day (a decimal serial such as 46157.75), SPICE datasets:
epochDate(decimalToInt(({Excel Date} - 25569) * 86400))
A decimal column holding whole days:
addDateTime(decimalToInt({Excel Date}) - 2, 'DD', parseDate('1900-01-01'))
Workbooks that use the 1904 date system:
addDateTime({Excel Date} + 1460, 'DD', parseDate('1900-01-01'))
Example
| Excel Date | Date |
|---|---|
| 45000 | 2023-03-15 |
| 46157 | 2026-05-15 |
| 46157.75 | 2026-05-15 18:00 |
| 61 | 1900-03-01 |
Why minus 2
In the default date system, Excel counts 1 January 1900 as day 1 and, for compatibility with Lotus 1-2-3, also counts 29 February 1900, a day that never existed. From 1 March 1900 onwards a serial number is therefore the number of days since 30 December 1899. Counting from 1 January 1900 instead means taking two days off: one because day 1 is 1 January itself, one for the phantom leap day. Dates before March 1900 come out one day off, which rarely matters in business data.
The second formula uses the same idea through Unix time. 25569 is the Excel serial number of
1 January 1970, the start of Unix time, and 86400 is the number of seconds in a day. The fraction of
the day becomes seconds, so 0.75 becomes 18:00. epochDate needs an integer and is documented for SPICE
datasets only.
The 1904 date system
Workbooks created with old versions of Excel for Mac can use the 1904 date system, where day 0 is
1 January 1904. The same date then has a serial number 1,462 lower: 15 May 2026 is 44695 instead of
46157. Hence + 1460 in place of - 2. If converted dates come out four years and one day early, the
file uses the 1904 system. Excel shows the setting under Options → Advanced → Use 1904 date
system.
Fix it once
Add the field in the dataset, not in each analysis, and hide the numeric column. Better still, fix the source: format the column as a date in Excel before saving, or convert it in the query that feeds the dataset, so the number never reaches QuickSight.
Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.
