Excel NOW Function – How To Use

The NOW function is categorized as a DATE/TIME function in Excel. It's a volatile function that returns the current date and time. Since the function is volatile, refreshing or reopening the Excel sheet updates the data.

Financial analysts find the NOW function helpful while generating KPI reports as it displays the date and time value and keeps refreshing it as and when new data is entered.

Syntax

The syntax of the NOW function is as follows:

Arguments:

None – The NOW function does not require any arguments. The name of the function is followed by empty parentheses.

Important Characteristics of the NOW function

• The NOW function is volatile. Any changes in the worksheet or a refresh will trigger the NOW function's output to update automatically.
• If your sheet doesn't update, you can use the F9 key to force the worksheet to update values. If it doesn't work, check if automatic recalculation is disabled in Formulas > Calculation Options > Automatic.
• The NOW function returns a valid Excel date and time value. If your output displays a serial number with a decimal value, check the cell's formatting.
• If you want to enter a static date and time value in Excel, use Ctrl + Shift + ;
• If you only want to enter today's date and not the current time, use the TODAY() function instead of the NOW function.

Examples

Let's try to see some examples of the NOW function.

Example 1 – Plain Vanilla Formula for the NOW Function

Let's get started with a simple bare-bones example of the NOW function. Since the function takes no arguments, we need absolutely no data to use the NOW function. Just insert the following text into a cell and you should see the date and time value:

Note that whatever output you see right now will change as soon as you make changes to the worksheet or reopen the worksheet later. The NOW function's output is dynamic and keeps updating.

Example 2 – Basic Operations with the NOW Function

You can also use the NOW function with some basic arithmetic operations. For instance, you could calculate the time 10 days and 6 hours  from now, like so:

Note: 6 hours is written as .25 since it accounts for 25% of the total hours in a day.

Similarly, you can add or subtract any number of days or hours, though calculations may require some manual effort. For instance, if you wanted to subtract 15 days and 18 hours, you'd need to add 15.75 (i.e., 15 days + (18 ÷ 24) hours) to the NOW functions output.

Example 3 – Using NOW Function With EDATE Function

You can nest the NOW function in several other functions to relay the current date and time value. However, not all functions will preserve the time component.

For instance, let's say you want to calculate a time precisely 2 months from now. To do this we can make use of the EDATE function. If you use the following formula, the time component will be truncated and the function will return only the date component after the calculation:

If you want to make calculations down to the last second, you'll need to use the following formula:

Let's look at why this function preserves the time component. The part of the formula before the plus sign is just the formula we used previously. The first formula will only return a date value. However, you can use the MOD function to add the time component.

You know by now that the time component in Excel is a decimal value. The MOD function calculates this decimal value by returning the remainder of a division between two numbers (i.e., the value of the now function and 2). For instance, MOD(10,3) returns 1.

NOW vs TODAY Function

The NOW function is a more comprehensive variant of the date-only TODAY function. The TODAY function returns only the date value, while the NOW function returns the date as well as the time value. They share several similarities. For instance, both functions are volatile and take no arguments.

That said, both functions have a defined set of uses. What's important is to be consistent with the use of these functions.

NOW Function & Timezones

Till now we have seen how to use the NOW function in Excel. One thing that should be evident by now is that the NOW function always returns the time based on the local timezone.

To convert the output of the NOW function for different timezones we can make adjustments based on a formula as:

In the above formula, first of all, we are fetching the current date-time using the NOW function. Next, our task is to adjust this date-time as per the corresponding time zones, based on the adjustment factor.

The Adjustment factor is the difference between your local time zone and the desired time zone. For instance, in the second sample, UTC (Universal Time Coordinated) is 5 hours and 30 minutes behind IST (Indian Standard Time) and hence the adjustment factor is -5.5 (5 hours 30 mins = 5 + (30/60)).

The adjustment factor is then divided by 24. This is done so as to convert the adjustment factor to an excel recognized time format, we know that Excel represents time as a fraction value of 1. Based on this we treat 6:00 as 0.25 (6:00/24), 12:00 as 0.5 (12.00/24), 18:00 as 0.75 (18.00/24) and so on. This fraction is then added to the output of the NOW function which gives us the final result.

Add Static Date and Time Value in Excel

By now, you know that the NOW function's output changes each time you refresh or reopen a worksheet. However, what if you wanted to enter a static timestamp in a particular cell without having to type it out?

You don't need a formula, what you need is an Excel shortcut. Press Ctrl + ; (i.e., Control + Semicolon), hit Space, and press Ctrl + Shift + ; (Control + Shift + Semicolon).

If your worksheet already contains a few cells that have the NOW function, you can copy the current time (Ctrl + C) and use paste special (Alt + E + S + V) to make the value static. However, be sure to press F9 before copying the value if you want to update the value to the current time.

Alright Excel ninjas, that's the end of the NOW function. If you haven't already, go check out the tutorial on the TODAY function as well. While you play around with this function on your Excel sheet, we'll go and craft another Excel tutorial for you. Until next time…