3

When using Google Spreadsheet today, I noticed that when entering a date as a simple number sequence (e.g., 151127), and choosing date as format, it ended up being a completely different date than the one intended (i.e. not 2015-11-27, but rather 2313-10-08).

After a bit of tinkering, I realized that each unit represented one (1) day from 1899-12-30, meaning that 151127 (onehundred-and-fifty-one-thousand-onehundred-and-twentyseven) is that many days from 1899-12-30.

My question is:

Is there a specific reason for using days as unit for this kind of date entry and, if so, what is it?

| improve this question | |
  • 1
    I expect that it was copied from the way Excel, and other spreadsheets before it, work. Spreadsheets are about calculations. I expect it's easier to work with dates if they're stored as integers. – ale Nov 30 '15 at 15:42
2

The zero mark for date/time in Google Sheets is 12/30/1899 0:00:00, as you can see by entering 0 in a cell and formatting it as date/time. That said, sheets are mostly 1-based, so the idea here is that day 1 is the last day of 1899.

Spreadsheets are designed for office workers, not for programmers. In an office, time is measured in days more often than in seconds or milliseconds. Measuring time in days allows one to use, e.g., =A2+1 to get next day after the date specified in A2. Using =A2+86400 for the same purpose would not be as intuitive for the target audience.

For the same reason, the time refers to local timezone, not UTC.

| improve this answer | |
  • Ah, my bad. I saw that it was 1899 when I tinkered, but when I wrote up the question, my mind went straight to Unix Epoch. I'll revise the question. – Gunnar Södergren Nov 30 '15 at 14:35

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.