Dates are notoriously finicky to use in spreadsheets. You can’t type them into a formula and expect it to work. However, there are a few different ways to use them in formulas. Once you know how, using dates is much easier.
Contents
Method 1 – Surround Dates and Times with Quotes
For the first example, dates and times can be typed directly into a formula if you use quotes. The quotes let Google Sheets know that 12/31/16 is a date instead of 12 divided by 31 divided by 16.
Tickmark  Formula  Result 

1  =12/31/1712/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 3pm12/31/16 4pm  #ERROR! 
7  ="12/31/17 3pm""12/31/16 4pm"  364.9583333 
 1Sheets doesn’t know these are dates, so it performs division for every forward slash.
 2Using quotes allows you to subtract two dates, giving you the number of days elapsed.
 3Using quotes allows you to subtract two dates when you spell them out correctly, and it returns the number of days elapsed between the two dates.
 4Sheets can’t subtract these two times because they aren’t in quotes.
 5Subtracting these two times works as expected because we wrapped them in quotes. These times will work with and without a space between the number and the am/pm.
 6Sheets can’t subtract these two times because they aren’t in quotes.
 7Subtracting these two times works as expected because we wrapped them in quotes. These times will work with and without a space between the number and the am/pm.
Method 2 – Use Cell References
The second way to use dates or times in formulas is to enter them in separate cells and reference them with your formula instead of typing them directly into the formulas, as shown above.
A  B  C  D  

1  Start date  End date  Formula  Result 
2  12/31/16  12/31/17  =B2A2  365 
3  2/5/13  4/8/18  =B3A3  1888 
Method 3 – Use Date or Time Functions
Dates have their own family of functions that allow you to work with them differently. Below are examples of a few of them. You can embed these functions inside other functions to create a formula.
Formula  Purpose 

=DATE(2001,3,28)  Converts three numbers into a valid date format. 
=TIME(11,23,49)  Converts three numbers into a valid time format. 
=NOW()  Returns the date and time right now. It constantly changes as time progresses. 
=TODAY()  Returns the date right now. It will change 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.
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.