Dates are notoriously finicky to use in spreadsheets. You can’t type them directly 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.

### Use quotes

For the first example, dates and times can be typed directly into a formula as long as 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/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 forward slash.**2**-Using quotes allows you to subtract two dates, giving you the number of days elapsed.**3**-Using quotes allows you to subtract two dates when you spell them 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 we wrapped them 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 we wrapped them 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 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 how you would do it.

A | B | C | D | |
---|---|---|---|---|

1 | Start date | End date | Formula | Result |

2 | 12/31/16 | 12/31/17 | `=B2-A2` | 365 |

3 | 2/5/13 | 4/8/18 | `=B3-A3` | 1888 |

### Use a date function

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 data into valid date 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.

Is there anyway to use a function in order to keep track of when each column has been edited? For example for keeping inventory – I need to know when the things are being added and subtracted to inventory and the date and time of when that happened.

Thanks

You could right-click on the cell and look at its revision history or look at the version history of the entire file.

Good Afternoon,

I am using the NOW() function in a formula to automatically provide the time another cell is edited . It does provide the information required but it continues to change ALL on the NOW() formulas on the complete column instead of the 1 required cell. How can I use the function for a single cell and separate each one of the cells from the column to provide each additional edited time .