Contents
How Dates Work
Though it may not be obvious initially, spreadsheets store dates as numbers instead of text. You can show dates in many different formats to the user, but their underlying value doesn’t change.
To illustrate the concept of the format changing but not the value, all of the rows in this table consist of 42734 displayed differently.
42734  No formatting applied 
12/30/2016  Format > Number > Date 
12302016  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… 
Using Dates in Formulas
If you use dates in formulas, you must be careful to use them correctly. If you write something like =12/31/1512/31/14, the result will be .002, which wouldn’t be what you were looking for. Sheets will think you are dividing because of the slashes instead of showing dates. A similar error will occur if you try =123115123114
. The separators for dates, slashes or hyphens, are also arithmetic operators.
To work around this, use quotes to surround them. Let’s look at a few examples. The first example is misinterpreting slashes as division operators. The second example is misinterpreting dashes as subtraction operators. The formula is written and interpreted correctly using quotes to surround each date value until the third example.
0.000860215053763443  Written as =12/31/1512/30/15 
91  Written as =123115123015 
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 nonUS format, you may get the error. Let’s take December 31, 2016
, as an example.
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 were formatted as a date, it would display “,” 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) 
If want the duration between two times on different days, consider using our TIMEDIF icon. Otherwise, it is difficult to arrive at the correct value.
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 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
Combining the two is easy once you understand how dates and times work.
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 that you can study and use them anywhere you like.
Related Tutorials

Find the Weeks and Days Between Two Dates
Learn how to find the weeks and days between two dates.

Find the Number of Weeks Between Two Dates
Learn how to find the number of weeks elapsed between two dates.

Find the Days and Hours Elapsed Using Google Sheets
Find the number of days and hours elapsed between to times.