ETL: ExcelSerialToDate

Convert an Excel serial date number (1900 date system) to a UTC datetime value. Use when ingesting spreadsheet exports that store dates as numbers.

Description

Converts an Excel serial date number (1900 date system) to a datetime data type. The integer part is the number of days since 1899-12-30; the fractional part is the time of day. The result is in UTC.

Use this function when a source — typically a spreadsheet export — represents dates as numeric serial values instead of formatted date strings.

Syntax

ExcelSerialToDate(serial_number)

Arguments

• serial_number - numeric value representing an Excel serial date. The integer part is the number of days since 1899-12-30; the fractional part is the time of day.

Examples

• ExcelSerialToDate(43831) returns datetime value 2020-01-01T00:00:00Z
• ExcelSerialToDate(43831.5) returns datetime value 2020-01-01T12:00:00Z

Return value datatype

datetime

Impact of null value

If serial_number is null, returns null.

Related functions

• DateToExcelSerial — the inverse conversion.
• ToDate — parse datetime values from strings or Unix milliseconds.