What Is NOW Function In Google Sheets?
The NOW Function in Google Sheets provides the current date & time, i.e., the date on the day the formula is executed and the simultaneous time. It helps users to add/subtract dates & time, calculate months and years before or after a certain date & time and calculate the age or tenure.
The Google Sheets NOW function doesn’t need any arguments, the formula itself gives the current date & time. And once entered, every time the formula is executed or the worksheet containing the formula is opened, the current date & time updates automatically.
For example, we will find the current date & time using the NOW() formula in the worksheet, as shown below.
Select cell A1, enter the formula =NOW() and press “Enter”, as shown below.
The output is 24 October 2024 and time is 5:42pm in the preset date & time format, as shown above.
Remember that anytime the worksheet is opened, say if we opened it tomorrow, then, it gets refreshed and updated. And we will get tomorrows date and the simultaneous time, because of the formula used.
[Special Note: In this article, the date values are entered in the Date & time format because the results of the NOW() formula will appear in the Google Sheets default Date & time format. If the format does not match, then, we will get incorrect results.]
Key Takeaways
- The NOW function in Google Sheets is a Date function, that returns the present date & time, and is independent of any arguments. The function itself returns results.
- We can use the following keyboard shortcut “Ctrl+Alt+Shift+;”, to enter today’s date & time in Google Sheets as a static, unchangeable value.
- We can use the functions such as IF(), DATEDIF(), IFS(), TEXT(), etc along with the NOW functions to derive the required results as tenure of an employee, the event date approaching, etc.
- The Conditional formatting technique helps in formatting the current date. And since the rule is applied with the formula, regardless of the constant updating of the sheets the rule will always apply to the formula applied cells.
Syntax
The syntax of NOW Formula in Google Sheets is,
The NOW Formula in Google Sheets does not have any arguments. So, one can execute as follows:
- Enter the formula, open the brackets and press “Enter”, or
- Enter the formula with both the open and close brackets and press “Enter”.
How To Use NOW Function In Google Sheets?
We can use the NOW Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets Ribbon.
Step 1: Choose an empty cell for the output à select the “Insert” tab à click the “Function” option right arrow à click the “Date” option right arrow à select the “NOW” function, as shown below.
Step 2: The “NOW” formulaappears, as shown below. There is no requirement to enter the argument as cell reference or cell values, just press “Enter”.
Method #2 – Enter the Formula in the Worksheet Manually.
Step 1: Select an empty cell for the output.
Step 2: Type =NOW( in the cell, as shown below. [Alternatively, type =N or =NO and double-click the NOW from the Google Sheets suggestions.]
Step 3: There is no requirement to enter the argument as cell reference or cell values, just press “Enter”, to view the outcome.
Examples
We will consider some NOW function in Google Sheets examples to understand the formula better.
Example #1 – Automatically Timestamp when Data is Entered in a Specific Cell.
We have an empty dataset given below where we can enter 5 cells of data in column A and retrieve 5 results using a formula in column B. We will insert the NOW() formula with IF() in column B to automatically timestamp when data is entered in a specific cell, here column A cells.
The steps to automatically timestamp when data is entered in a specific cell using the NOW() and the IF() functions are,
Step 1: Select cell B2, enter the formula =IF(A2=””,””,NOW()), press “Enter” and drag the formula from cells B2 to B6 using the fill handle, to get the following results.
As we notice the dataset is still empty, but the formula is already applied to the result cells. It is because the condition is only when we enter data, then the timestamp will appear.
Step 2: Let us enter some random values in column A.
- Select cell A2 and type “abcde”. We can see that the timestamp in the corresponding column automatically appeared. Now, we can enter the rest of the values. However, we must remember that any changes will also affect the previous, i.e., the cell B2’s value. If we so not want that data to get updated, then once the timestamp appears, then we can copy-paste the data as values and not as the formula result.
- Next, select cell A3 and enter 12345
Step 3: Let us copy-paste the B2 and B3 values as values and not as formula results. Therefore, select cell B2 and B3, press “Ctrl+C” to copy the values and “Ctrl+Shift+V” to paste as values or select the path shown below.
Step 4: Now, add the rest of the values, as shown below to get the results shown below. We will also leave a cell blank.
The cell values of B2 and B3 have not changed, the blank cell value in cell A3 has an empty result cell in cell B3, as per the formula and the rest of the cells B5 and B6 cell values are the formula results.
Example #2 – Calculate the Tenure of Employees.
Consider the employee details dataset given below that consists their names, ID’s, department, job role, date of joining, etc. based on which we will calculate the tenure of employees using the NOW() and the DATEDIF() functions.
The steps to calculate the tenure of employees using the NOW() and the DATEDIF() functions are,
Step 1: Select cell G2 and enter the formula =DATEDIF(F2,NOW(),”Y”), as shown below.
Step 2: Press “Enter” and drag the formula from cell G2 to G10 using the fill handle, to get the following results.
Example #3 – Check if a Task or Event is Approaching.
The dataset given below is a corporate event or task list with its designated dates. Let us check if a task or event is approaching using the IF() and the NOW() functions.
The steps to check if a task or event is approaching using the IF() and the NOW() functions are,
Step 1: Select cell C2 and enter the formula =IF(B2>NOW(),”Event Approaching”,”Elapsed”), as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C6 using the fill handle, to get the following results.
Example #4 – Highlight Cells Based on the Current Date.
The dataset given below consists of some random date values where in the cells A2, A3, A5 and A7 we have applied the NOW() function and the cells A4 and A6 are direct values. Let us highlight cells based on the current date using the Conditional Formatting method, i.e. only the cells with the formula, that returns the current date and the simultaneous time, whenever the formula is executed, must be highlighted.
The steps to highlight cells based on the current date using the Conditional Formatting method are,
Step 1: Select cells A2:A7 à select the “Format” tab à click the “Conditional formatting” option, as shown below. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: In the “Conditional format rules” pane, we see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab,
- Select the “Custom formula is” option from the “Format cells if…” drop-down and enter the formula =A2=NOW() in the “Value or formula” field that appears.
- Select the required highlight color from the “Formatting style”, here, cyan.
- Finally, click the “Done” option.
We get the following dataset with the current date highlighted.
Important Things To Note
- The NOW Google Sheets function is volatile because every time we open a worksheet, it updates itself continuously. To avoid the automatic updating of the Date, we can copy the formula results and paste it as a value.
- We will get an incorrect date value or 1900’s year dates, when we find the difference between the dates if the first date value is less than the second date value. Therefore, ensure to always enter the greater value first and then subtract it from the lesser date value.
- If the values like total dates or difference returns some date value instead of a numeric whole number, then change the cell format as “Format à Number à Number”.
Frequently Asked Questions (FAQs)
The NOW in Google Sheets might not work due to the following reasons;
• The function is entered without brackets. Ensure always to type atleast the open brackets as NOW(, if not both the brackets.
• Cell values or cell references are provided when the function works without any argument.
• The cell format is not in par with the calculated result. Therefore, either change the cell format initially before executing the formula or after the results are calculated.
• The worksheet gets updated automatically to the current date every time we open, we might feel the values have changed.
We often forget in which category a function falls, here, the “NOW” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “NOW” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the NOW in Google Sheets by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “NOW” function, as shown below.
Download Tempalte
This article must help understand NOW Function in Google Sheets with formula and examples. You can download the template here to use it instantly
Recommended Articles
Guide to What is the NOW Function in Google Sheets. We learn its syntax & how to use it to find the current date and time with examples. You can learn more from the following articles. –
Leave a Reply