Dates are notoriously finicky to use in spreadsheets. You can't type them directly into a formula and expect the formula to work. However, there are a few different ways that you can use them in formulas. Once you know how, using dates is much easier.

Use quotes

Dates and times can be typed directly into a formula as long as you use quotes. The quotes let Goolge Sheets know that 12/31/16 is a date instead of 12 divided by 31 divided by 16.

TickmarkFormulaResult
1=12/31/17-12/31/16-0.001423149905
2="12/31/17"-"12/31/16"365
3="December 31, 2017"-"December 31, 2016"365
4=4pm - 2am#ERROR!
5="4pm"-"2am".583333333
6=12/31/17 3pm-12/31/16 4pm#ERROR!
7="12/31/17 3pm"-"12/31/16 4pm"364.9583333
  • 1 Sheets doesn't know these are dates so it performs division for every /.
  • 2 Using quotes allows you to subtract two dates and it gives you the number of days elapsed.
  • 3 Using quotes allows you to subtract two dates when they are spelled out this way and it returns the number of days elapsed between the two dates.
  • 4 Sheets can't subtract these two times because they aren't in quotes.
  • 5 Subtracting these two times works as expected because they are wrapped in quotes. These times will work with and without a space between the number and the am/pm.
  • 6 Sheets can't subtract these two times because they aren't in quotes.
  • 7 Subtracting these two times works as expected because they are wrapped in quotes. These times will work with and without a space between the number and the am/pm.

Use cell references

The second way to use dates and/or times in formulas is to enter the dates in separate cells and reference them with your formula instead of typing the dates directly into the formulas as shown above. When working with tables of data, this is commonly the way that it would be done.

ABCD
1Start dateEnd dateFormulaResult
212/31/1612/31/17=B2-A2365
32/5/134/8/18=B3-A31888

Use a date function

Dates have their own family of functions that allow you to work with them in different ways. Below are examples of a few of them. These functions can be embedded inside other functions in order to create a formula.

FormulaPurpose
=DATE(2001,3,28)Converts data into valid date format.
=NOW()Returns the date and time right now. Constantly changes as time progresses.
=TODAY()Returns the date right now. Will changes as time goes on.

 Live example in Sheets 

Go to this spreadsheet for a live version of the above data, so that you can study and use them anywhere you like.