How dates work

Spreadsheets, including Google Sheets, store dates as numbers. Dates can be shown in many different formats to the user, but their value is still just an integer. All of the rows in this table are the integer 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, it is imperative that you 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 that you are dividing with the slashes instead of showing dates. A similar blow 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 they are surrounded by quotes.

-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"

Video showing how dates and times work in Sheets

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 locale.
Screen capture of the spreadsheet settings

Change your locale in the settings

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 is not able to 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 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 result is formatting as an integer. If the result was formatted as a date, it would display as 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 formatted in different ways in columns 2 and 3. 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 they will be represented by negative integers.

Formatted as date Formatted as 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 similar to dates in that they are shown as times but are really numbers. One hour is .0417 which is the result of 1 hour divided by 24 hours in a day.

3:00 AM Typed as 3am, default formatting
9:00 PM Typed as 9pm, default formatting
0.125 Typed as 3am, changed formatting to Number -> Automatic
.875 Typed as 9pm, changed 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 3am, default formatting
42735.125 Typed as 12/31/16 3am, changed formatting to Number -> Automatic

 Live example in Sheets 

Go to this spreadsheeet for a live version of these example that you can study and use anywhere you would like.