Google Spreadsheets NOW Function in Date and Time Calculations

Add the current date and time to a Google Spreadsheet

Add the Date or Time with the NOW function in Google Spreadsheets
Add the Date or Time with the NOW function in Google Spreadsheets. © Ted French

Google Spreadsheets Date Functions

There are a number of date functions available in Google Spreadsheets. Depending on your needs, you can use a date function to return, among other things, the current date or the current time.

Date functions can also be used in formulas to subtract dates and times – such as finding dates that are so many days past the current date or so many days in the future.

Google Spreadsheets NOW Function

One of the better known date functions is the NOW function and it can be used to quickly add the current date – and time if desired – to a worksheet or it can be incorporated into a variety of date and time formulas as discussed below.

NOW Function Examples

The NOW function can be combined with a number of functions to create a variety of date formulas as shown in the image above.

By row, the purpose of these formulas are:

  • Row one: returns the number of days until Spring 2016;
  • Row two: returns the number of days since Christmas 2015;
  • Row three: returns the date five days ago;
  • Row four: returns the number of days until the end of the current month;
  • Rows five and six: return the current time;
  • Rows seven, eight, and nine: return the current hour (24-hour clock), minute, and second;
  • Row ten: returns the date and time 12 hours ago (times are stored as fractions of whole days).

NOW Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the NOW function is:

= NOW ( )

Note: There are no arguments – the data normally entered inside the function's round brackets – for the NOW function.

Entering the NOW Function

Since there are no arguments for the function, NOW can be quickly entered. Here's how:

  1. Click on the cell where the date/time will be displayed to make it the active cell.
  2. Type: =Now() into that cell.
  3. Press the Enter key on the keyboard.
  4. The current date and time should be displayed in the cell where the formula was entered.
  5. If you click on the cell containing the date and time, the complete function = NOW ( ) appears in the formula bar above the worksheet.

Shortcut Keys to Formatting Cells for Dates or Times

To display just the current date or time in the cell, change the format of the cell to either the time or date format using the following keyboard shortcut keys:

  • Format date shortcut (day/month/year format):
    Ctrl + Shift + # (hash tag or number sign key) 
  • Format time shortcut (hour:minute:second AM/PM format):
    Ctrl + Shift + @ 

Formatting the NOW Function using the Format Menu

To use the menu options in Google Spreadsheets to format the date or time:

  1. Select the range of cells you'd like to format or modify;
  2. Click on Format > Number > Date/Time.

The formats applied to dates and times using this method are the same as those applied using the formatting shortcuts.

The NOW Function and Worksheet Recalculation

The NOW function is a member of Google Spreadsheet's group of volatile functions, which, by default, recalculate or update every time the worksheet in which they are located recalculates.

For example, worksheets recalculate each time they are opened or when certain events occur – such as entering or changing data in the worksheet – so if the date and/or time is entered using the NOW function, it will continue to update.

Spreadsheet settings – located under the File menu in Google Spreadsheets – has two additional settings for when a worksheet recalculates:

  • On change and every minute.
  • On change and every hour.

There is no option within the program for turning off recalculation of volatile functions.

Keeping Dates and Times Static

If having the date and/or time continually change is not desirable the options for entering static dates and time include typing the date/time in manually, or entering them using the following keyboard shortcuts:

  • Date shortcut:
    Ctrl + ; (semi-colon key) 
  • Time shortcut:
    Ctrl + Shift + : (colon key)