How dates work
Spreadsheets store dates as numbers. You can show dates in many different formats to the user, but their value is still just an integer. All of the rows in this table consist of 42734 displayed differently.
| 42734 | No formatting applied |
| 12/30/2016 | Format -> Number -> Date |
| 12-30-2016 | Format -> Number -> More Date and Time Formats… |
| 12/30 | Format -> Number -> More Date and Time Formats… |
| Friday, December 30, 2016, 12:00:00 AM | Format -> Number -> More Date and Time Formats… |
If you are using dates in formulas, you must understand this concept. If you write something like =12/31/15-12/31/14, the result will be -.002, which surely wouldn’t be what you were looking for. Sheets will think you are dividing because of the slashes instead of showing dates. A similar thing out will occur if you try =12-31-15-12-31-14. The separators for dates, slashes or hyphens, are also arithmetic operators. The spreadsheet cannot interpret dates unless quotes surround them.
| -0.000860215053763443 | Written as =12/31/15-12/30/15 |
| -91 | Written as =12-31-15-12-30-15 |
| 1 | Written as =”12/31/15″-“12/30/15” |
Tip: If you are not in the United States, you may need to change your spreadsheet settings to show dates in the correct format for your location.
If you enter dates in a non-US format, you may get the #VALUE! error. If you hover over the error, the explanation says, “Function MINUS parameter 1 expects number values. but ’31/12/16′ is a text and cannot be coerced into a number.” In other words, Sheets cannot convert 30/12/16 to 42,734, nor can it convert 31/12/16 to 42,735, so it cannot subtract the two values.
| entered as MM/DD/YY | entered as DD/MM/YY | |
| start | 12/31/16 | 30/12/16 |
| end | 12/31/16 | 31/12/16 |
| elapsed time | 1 | #VALUE! |
Subtracting two dates: Example 1
The same value is formatted in different ways in columns 2 and 3. The elapsed time is subtracting the two values. The result is the same regardless of the format of the cells above. Also, note that the spreadsheet formatted the result as an integer. If the result was formatted as a date, it would display “12/31/1899” which would not make sense for this operation.
| Formatted as date | Formatted as number | |
| start | 12/30/16 | 42,734.00 |
| end | 12/31/16 | 42,735.00 |
| elapsed time | 1 | 1.00 |
Subtracting two dates: Example 2
The same value is formatted in different ways in columns 2 and 3. It is similar to example one, but you should look at this one too, just for giggles. Notice the extra day for leap year.
| start | 12/31/15 | 42,369.00 |
|---|---|---|
| end | 12/31/16 | 42,735.00 |
| elapsed time | 366 (2016 was a leap year) | 366.00 (2016 was a leap year) |
The beginning of time
According to Google Sheets, time started on December 31, 1899. However, the dates before this will still exist, but negative integers will represent them.
| Formatted as a date | Formatted as a number |
|---|---|
| 12/29/1899 | -1.00 |
| 12/30/1899 | 0.00 |
| 12/31/1899 | 1.00 |
| 1/1/1900 | 2.00 |
How times work
Times work similarly to dates in that they are shown as times, but they are actually numbers. One hour is Times work similarly to dates in that the spreadsheet shows them as times, but they are numbers. One hour is .0417, which results from 1 hour divided by 24 hours in a day.
| 3:00 AM | Typed as 3 AM, default formatting |
| 9:00 PM | Typed as 9 PM, default formatting |
| 0.125 | Typed as 3 AM, changed the formatting to Number -> Automatic |
| .875 | Typed as 9 PM, changed the formatting to Number -> Automatic |
How dates and times work together
Once you understand how dates and times work, combining the two is easy.
| 12/31/16 3:00 AM | Typed as 12/31/16 3 AM, default formatting |
| 42735.125 | Typed as 12/31/16 3 AM, changed the formatting to Number -> Automatic |
Live examples in Sheets
Go to this spreadsheet for a live version of these examples, so you can study and use them anywhere you like.