Shape · Dates and time

Convert Serial Date

Converts between numbers and dates: Unix timestamps, Excel serial dates, Julian days or your own starting point. For example, 45292 from Excel becomes 2024-01-01.

Also known as: Epoch, Excel date

How it works

Each conversion row names a column, a format (Unix epoch, Excel / OLE, Julian or Custom epoch) and a direction, To number or To datetime. Unix takes a unit (seconds by default, or milli-, micro- or nanoseconds); Excel takes the 1900 or 1904 system; Julian takes Julian Day (JDN) or Modified (MJD); Custom epoch takes an anchor date and a unit, with presets such as NTP, Apple / Cocoa, Windows FILETIME and .NET ticks. To datetime can produce a Timestamp (default) or a Date. The result is appended as a new column, named column_format for To number and column_datetime for To datetime unless you set an output. In the Excel 1900 system the serials 0 and 60 are not real dates and decode to NULL, with a warning that counts them.

Inputs and outputs

Settings

conversions: [{column, format:"unix"|"excel"|"julian"|"custom", direction:"to_serial"|"from_serial", unixUnit?, excelSystem?, julianKind?, customAnchor?, customUnit?, decodeType?, outputColumn?}]

Example

A spreadsheet export stores shipped as an Excel serial. Add a conversion: column shipped, Excel / OLE, 1900 system, To datetime, output type Date.

More in Dates and time

• Extract Date/Time Parts — Pulls parts out of a date into new columns: year, quarter, month, week, day, weekday, hour, a weekend flag and more.
• Date/Time Arithmetic — Adds or subtracts time from a date, or measures the gap between two dates, from seconds to years or working days. For example, due date = order date + 30 days.
• Truncate Date/Time — Rounds dates down to the start of a period, such as the first of the month. Handy before grouping by month.
• Time of Day — Drops the date from a timestamp and keeps the clock time, so you can compare times across days.
• Make Date from Parts — Combines separate year, month and day columns (and hour, minute, second) into one real date or timestamp.
• Convert Date/Time to Text — Turns a date into text using a pattern, such as 01/03/2024 or Mar 2024. The result is text, so use it for labels and exports, not for date math.
• Convert Timezone — Shifts times from one time zone to another, with daylight saving handled. For example, UTC to Europe/Warsaw.