Add 64-bit Unix time as a data type
Excel currently represents dates, times, and datetimes as double-precision floating-point numbers that store the number of days since a particular date. This has many problems:
• The user will encounter precision issues when they deal with times and datetimes. This issue only gets worse for datetimes over time as a greater number of the limited significant digits are required for storing the date, leaving fewer digits for the time. The precision issues require users to set up their equality tests with a tolerance in mind and generate their timestamps in a numerically stable way. This is too much detail for the average user.
• If the formatting is removed from a date, time, or datetime (perhaps from copying and pasting as values or from clearing all formatting on a messily formatted sheet), it will appear as a number, which will confuse users that do not know about the serial number representation of date, times, and datetimes.
• Excel does not support dates earlier than 1900.
Clearly, we need a better system.
Please add 64-bit Unix time as a data type. 64-bit Unix time is based on seconds and not days, so it does not have precision issues like Excel's serial numbers do. It also supports a much wider range of timestamps. 64-bit Unix time is also the standard for tracking time in software, so if Excel supports it, Excel will be consistent with other programs and it will avoid the need (in the long run) to maintain code that converts between Excel's serial numbers and 64-bit Unix time.
When this data type is implemented, allow the timestamp to display differently by specifying the time zone as part of the formatting. For example, through formatting, the timestamp "2019-01-01 23:00" (EST) should be able to display as "2019-01-01 20:00" (PST) without changing the stored value. Please also overload the current "Date & Time" functions to accept 64-bit Unix time, and throw an error if nonsensical operations are attempted on timestamps (e.g. multiplication, exponentiation).
I just learned about ticks. They are also 64-bit but span from the start of the year 1 to the end of the year 9999 and have a precision down to 100 nanoseconds. Since 64-bit Unix time does not support subseconds and Excel does support subseconds, ticks might be a better successor to Excel's serial numbers.
A.C. WILSON commented
Agree. I too have had to set up date-time equality tests with a tolerance, and with comments to explain them to my successors.
BTW, to be precise, Excel does not support ACCURATE dates earlier than March 1, 1900, due to the "leap-year 1900" error which, for consistent results, Excel carried forward from Lotus-1-2-3 . (As many already know, in the Gregorian calendar 1900 and 2100 are leap years, although 2000 was not.)
Related (suggestion for a date literal syntax):