Datatypes › Date and time › datetime
Date and time as an Excel serial number
ID excel_serial_datetime
Pattern of datetimeNot PIIDraft
This record is a draft: it has not been reviewed yet and may be incomplete or wrong. Do not rely on it for compliance decisions. Help improve it (see CONTRIBUTING.md).
Point in time written as the serial number of spreadsheets: the whole part counts the days since 30 December 1899 and the fraction is the part of the day that has passed, as in 45175.42585648148 for 6 September 2023, 10:13:14, or 31911.416666666668 for 14 May 1987, 10:00. Sheets exported without a date format keep it: the Dataverse .tab export of an Excel file writes the timestamps of a survey or the readings of a gauge this way. A whole number is a date only (excel_serial_date).
- Pattern of
- Date and time
datetime - Categories
- Date and time
- Countries
- Global (not tied to a country)
- Languages
- Common to most languages
- Quality
- 90/100 · missing: mapping (Wikidata, Schema.org, ...)
- Metacrafter rules
- 1 rule (1 by field name, 0 by value)
excelserialdatetimebynamefield, common/dateandtime.yaml
- Supported by tools
- metacrafter
Regular expression
^[1-5]\d{4}\.\d{1,17}$
Python re syntax.
Test a value
Examples
| Value | Description |
|---|---|
| 45175.42585648148 | 6 September 2023, 10:13:14, a survey response (datecreated, RDA-UNR) |
| 31911.416666666668 | 14 May 1987, 10:00, a gauging at the Pavón stream (Fecha y Hora) |
| 44927.5 | 1 January 2023, noon |
| 45175 invalid | a whole day: excel_serial_date |
Translations
| Language | Name | Description |
|---|---|---|
| Spanish | Fecha y hora como número de serie de Excel |
Sources
- doc: support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252
- wikipedia: Spreadsheet (en.wikipedia.org)
Use this record
- Page URL:
https://registry.apicrafter.io/datatype/excel_serial_datetime - JSON: API v1 · legacy shape (used by metacrafter) · Markdown (for LLMs)
- Improve it: edit
data/datatypes/any/datetime/excel_serial_datetime.yamlon GitHub