Search notes:

Excel: Serial number

Excel stores a date and/or time as a 64-bit floating point number which stores the (fractional) number of days between December 31st 1899 and the date stored.
This number is referred to as serial number.
Because Excel has a bug and incorrectly treats the year 1900 as a leap year, it also counts 29th of February 1900 (which is an inexisting date).
However, the year 2100 is correctly treated as a non leap year (Thanks Marc T. for this observeration).
Thus, the serial numbers become:
Date Ser. nr.
1899-12-31 0
1900-01-01 1
1900-01-02 2
… …
1900-01-31 31
1900-02-01 32
… …
1900-02-28 59
1900-02-29 60
1900-03-01 61
1900-03-02 62
… …
2009-07-05 39999
2009-07-06 40000
2009-07-07 40001
… …
2021-09-05 44444
… …
2100-02-28 73109
2100-03-01 73110

Fractional part of serial number

The serial number's fractional part specifies the portition of a day for which time information is stored.
So 2009-07-06, 06:00 AM is stored as the serial number 40000.25

Relation to OLE Automation date representations

An OLE Automation date is a double-precision floating-point number that represents a date as the number of days before or after the base date, midnight, 30 December 1899 and thus is related to Excel's serial number: for values greater than or equal to 61, an OLE Automation date value corresponds to the same date as the same serial number in Excel.
The .NET type System.DateTime provides the methods FromOADate and ToOADate to convert between an OLE Automation date and a System.DateTime value, as is shown in the following simple PowerShell snippet: 
PS C:\> [System.DateTime]::FromOADate(60)

Wednesday, February 28, 1900 12:00:00 AM

PS C:\> [System.DateTime]::FromOADate(61)

Thursday, March 1, 1900 12:00:00 AM

PS C:\> [System.DateTime]::FromOADate(44444)

Sunday, September 5, 2021 12:00:00 AM

Convert an Oracle Date to an Excel Serial Number

For dates later than 1900-02-28, subtracting date '1899-12-30 from an Oracle date returns the date as en Excel serial number: 
select
   sysdate - date '1899-12-30'
from
   dual;

Misc

DAX date and time function return a real datetime data type, not a serial number.

See also

Exporting Oracle DATEs to Excel
Excel worksheet functions that convert from/to serial numbers.

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/Microsof...', 1758209743, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/Microsoft/Office/Excel/data-types/number/serial/index(119): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78