3

If you enter 1 in a cell and then format it as a date , you get a different date in a Google sheet (12/31/1899 0:00:00), from the same action in Excel (1/1/1900 0:00)

Is this an error ?

This is relevant to Historical or Sci-fi time travel Authors who use spreadsheets to create their plots and switch from Excel to Sheets and set them in the 1st 60 days of the 20th century.
Programmers who rely on 0 or 1 in a date returning the same text result.
That is all I can think of for now.

1

This is a known Excel's issue. See Excel incorrectly assumes that the year 1900 is a leap year. Some people could tell that this is not a "bug" because it's working as intended. See Is there a bug in Excel concerning dates?

On the Google Sheets side, there isn't documentation about why they don't "repeat the same error" but it could be related that they prefer to make it follow the OpenFormula standard.

Historical and Sci-fi time travel authors, alsos should have in mind that the calendar isn't the same all the time and for all the places and that some Excel and Windows versions have other Date and DateTime issues.

From https://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html

Date and DateTime

A Date is a subtype of number; the number is the number of days from a particular date called the epoch. Thus, a date when presented as a general-purpose number is also called a serial number. This specification does not specify the exact value of the epoch, but implementations MUST support all dates from January 1, 1904, through December 31, 9999 (inclusive). Portable spreadsheet files MUST NOT assume any particular epoch values. Since dates are simply numbers, they can be added, subtracted, and so on like other Numbers. Subtracting one date from another produces the number of days between the dates.

A Datetime is also a subtype of number, and for purposes of formulas it is simply the date plus the time of day.

Note: Excel for Windows usually uses 1/1/1900 as serial number 1, while Excel for Windows uses 1/1/1904 as serial number 1. "Excel 2000 in a Nutshell" page 330 discusses time storage in Excel, including this, and noting December 31, 9999 as a date both support. Excel 2003 copies a bug from an old version of Lotus 1-2-3; both act as though 1900 was a leap year. Thus 1900-02-29 has the serial number 60, and all date calculations on or before that date are wrong by one day. This specification does not require copying this bug. See "Excel 2003 Formulas" page 143.

Excel 2003 is unable to deal with dates before January 1, 1900; again, there's no requirement that other implementations have this limitation. Implementations that wish to support a broader range of dates, yet also the same numbers for most dates, could do so by using negative numbers as dates before the epoch (be careful, because a time inside the day adds to the beginning of the date).

In OpenDocument Format a date, datetime, or time value in a cell is stored in a special locale-independent format based on ISO 8601; see the OpenDocument specification for more information. Implementations may choose to store dates in a special type that is distinguishable from other numbers. However, from the point of view of a formula, a date, datetime, or time value is simply a subtype of Number, and must follow the rules of this specification. Most countries use the Gregorian calendar and ISO 8601, but not all. Note that applications must be able to convert text, in a variety of formats, into date values.


TBD: In earlier times dates were dependent on the location of the event, which is not necessarily the current locale. In particular, different countries switched from Julian to Gregorian on different dates. This creates a challenge if it is desired to represent dates in formulas significantly before 1900. One solution is to use the "proleptic Gregorian" calendar, which is simply the current Gregorian calendar indefinitely extended in both directions of time. Python 2.4's date types use proleptic Gregorian, and points to Dershowitz and Reingold's book "Calendrical Calculations" for various means to convert that to other calendar systems. The advantage of proleptic Gregorian is that it is locale-independent, works well with ISO 8601, and there are defined ways to convert between it and other calendars. If the goal is just to store dates, and not compute differences, then it can easily represent arbitrary dates without complexity in the basic spreadsheet implementation. If conversions are needed, they can be embedded in spreadsheet formulas -- which is the right place to put them, because the current locale is often not the locale of the event, and only the person entering the data will know the correct locale.

Other references

From Wikipedia:

0

It is because Excel is compatible to Lotus 123 and Google sheets is not.

There is a difference between Google Spreadsheets and Excel, but only until March 1st 1900.

Excel has a February 29th 1900, which did not exist as 1900 was not a leap year.

In Excel 0 returns a weird 1/0/1900 0:00 - sheets 12/30/1899 0:00
In Excel 1 returns 1/1/1900 0:00 - sheets 12/31/1899 0:00
In Excel 2 returns 1/2/1900 0:00 - sheets 1/1/1900 0:00 day This continues until
In Excel 60 returns the non-existant 2/29/1900 0:00 - sheets 2/28/1900 0:00
In both Excel and Sheets 61 will return 3/1/1900 0:00 and for all increasing values the dates will be the same.

The reason

Excel is compatible with Lotus 123 which has the same problems (features?).
Google, perhaps a child of Star Wars ("who is more the fool, the fool or fool who follows him", Obi-Wan) decided to be more rigorous.

  • 1
    What do you mean by "please, do not add answers"? If that is literal, then your Q&A is on the wrong place. By the other side it will be great if you add references. – Rubén Mar 12 '17 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.