Google Sheets NOW Function in Date and Time Calculations

Add the current date and time in Google Sheets

Add the Date or Time with the NOW function in Google Spreadsheets

Ted French

Google's spreadsheet program, Google Sheets, is free web-based software that is part of the Google Drive services. Similar to Excel, Google Sheets makes it possible to create, edit, and collaborate with others as you work on spreadsheets. An understanding of functions and how they work is essential to building useful spreadsheets.

Google Sheets Date Functions

Several date functions are available in Google Sheets. 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 a certain number of days in the future.

Google Spreadsheets NOW Function

One of the better-known date functions is the NOW function. It can be used to add the current date — and time, if desired — to a worksheet quickly, or it can be incorporated into a variety of date and time formulas.

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 that accompanies this article.

By row, the purposes of these formulas are:

  • Row 2 returns the number of days until Spring 2016
  • Row 3 returns the number of days since Christmas 2015
  • Row 4 returns the date five days ago
  • Row 5 returns the number of days until the end of the current month
  • Rows 6 and 7 return the current time
  • Rows 8, 9, and 10 return the current hour (24-hour clock), minute, and second
  • Row 11 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()

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 entered quickly. 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 keyboard shortcuts.

  • The format date shortcut (day/month/year format) is Ctrl+Shift+#.
  • The format time shortcut (hour:minute:second AM/PM format) is Ctrl+Shift+@.

Formatting the NOW Function Using the Format Menu

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

  1. Select the range of cells you want 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 Sheet'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 when data is entered or changed in the worksheet — so if the date or time is entered using the NOW function, it continues to update.

The spreadsheet settings, which are located under the File menu in Google Sheets, have 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 or time continually change is not desirable, the options for entering static dates and time include typing the date or time in manually or entering them using the following keyboard shortcuts:

  • The static date shortcut is Ctrl+; (semi-colon key)
  • The static time shortcut is Ctrl+Shift+: (colon key)