Manually typing the date and time in a spreadsheet can slow your progress down significantly. The Google Sheets NOW function allows you to automatically return the current time and date value, which should greatly increase your productivity if your spreadsheet requires multiple inputs of this value.
Why Use The Google Sheets NOW Function?
Although the NOW function seems like a simple spreadsheet addition, it works fantastically when building dynamic formulas.
For example, a pharmaceutical assistant could use the NOW function to keep track of the length of time between doses for a patient.
The NOW function makes it simple to see the exact date and time of the last dose and calculate the time between then and now.
To boil it down, you can use the Google Sheets NOW function to keep track of any data that requires an accurate time stamp.
The syntax of the NOW function is very simple but may require a little tweaking depending on your needs. Let’s take a look at how it works.
This function doesn’t require any arguments as it doesn’t take data from other cells or need to conduct any calculations. That means the syntax is one of the more simple ones to write in Google Sheets.
The syntax is:
In most functions, you have to place cell references or other arguments between the round brackets, but the NOW function does not require any inputs for its basic uses.
You can also use NOW calculations to figure out the time passed between another cell’s data and the current date and time. That’s when you would input an argument into the brackets.
Some Things To Keep In Mind When Using The NOW Function
The NOW function is what’s known as a volatile function in Google Sheets. That means the spreadsheet will update the field every time it’s edited.
You cannot turn off recalculations for volatile functions, but you can when the spreadsheet recalculates the value by going to File > Spreadsheet Settings > Calculations.
Some actions that often cause volatile functions to recalculate include:
- Hiding and unhiding rows (but not columns)
- Entering new data
- Adding and deleting rows
The NOW function shows the date and time of when the function was entered. It won’t change unless you update the sheet or edit worksheet recalculation rules.
NOW vs TODAY
Both of these functions work similarly, the difference being that the TODAY function only returns the date whereas NOW gives both the date and time. The NOW function also works similarly to automatically inserted timestamps.
How to Use the NOW Function to Add Date and Time
Let’s carry on with the example of keeping track of a patient’s doses. In the screenshots below, there is a basic spreadsheet that can keep track of such data.
- The first step is to select the cell that you want the NOW function to return data for. In the example below, we have chosen the cell with the first dose, B2.
- Then you just have to enter the basic syntax of =NOW() you could either manually type the whole function or select it from the drop-down menu. Since we are not placing any arguments, you can press enter to get the result.
How to Use the NOW Function to Identify Time Passed
Let’s say we wanted to use the above spreadsheet to identify how long a patent had been since their last dose, so we added a new column. To make the calculations more clear and give you a better understanding of the syntax, we have separated the date and time columns too.
We can use the NOW function to calculate how many days it has been since the patient’s last dose.
- Press =NOW() into the desired cell, which in this sample is C2
- Then, outside the first set of round brackets, press the – key
- Open a new set of brackets and click or type the date reference cell, which in this example is B2
- Press + then click or type the time cell which, in the example is C2
- Press enter
As you can see in the example, it gives an answer of approximately 31. That means it has been 31 days since the last dose.
In this next screenshot, we have dragged the syntax into the rows below the initial cell of B2. If you aren’t sure how to do this, you just have to:
- Click and hold the blue box in the bottom right of the highlighted cell
- Drag in the direction you want the formula to follow to
As you can see, the NOW function has given an exact number between the date and time given and the current values.
You might not want results given in days. After all, it gives quite a few decimal points and can be pretty confusing.
To get the results in hours, minutes, and seconds, highlight the cells you want to change the formatting for, then click Format > Number > Duration
After you’ve done this, the results will be much easier to interpret, as in the screenshot. You can see that it has been 744 hours, 33 minutes, and 22 seconds since the first dose — and so on for the other dates.
Be aware that if you can’t use another cell that is using the NOW function to calculate a new NOW function as it will update both cells. The original date and/or time must come from:
Having to use other functions may get in the way of your productivity. So, make sure you don’t use the NOW function to show original dates if you plan to use the NOW function to calculate how much time has passed later.
A Summary of the NOW Function
The Google Sheets NOW function is a surprisingly useful and complex tool masked as a simple function if used the right way. It may take a little while to wrap your head around the syntax and formatting to begin with. But, if you use the current date and time a lot in your business, using this function will save you a lot of time in the long run.
If you found this article useful, make sure you check out our other Google Sheets guides so you can become a master of spreadsheets.
Jake is a spreadsheet expert and content writer from New Zealand. He has a double Bachelor's Degree in Teaching and has been working in the education industry for over 11 years. His experience makes him adept at breaking down complex topics so that everyone is able to understand.