How to Use the NOW Function in Excel (8 Suitable Examples)

In Microsoft Excel, the NOW function is used to show timestamps. By enclosing the NOW function inside other related functions, different useful outputs can be obtained. In this article, you’ll get to learn how to use this NOW function efficiently in Excel under multiple criteria.

The above screenshot is an overview of the article which represents a simplified application of the NOW function in Excel. You’ll learn more about the dataset as well as the methods and functions under different criteria in the following sections of this article.

Introduction to the NOW Function

• Function Objective:

NOW Function returns with the current date and time formatted as a date and time.

• Syntax:

=NOW()

No argument is accepted within the NOW function.

How to Use the NOW Function in Excel: 8 Suitable Instances

1. Adding or Subtracting Days with the NOW Function

In the picture below, Column B represents the current date and time repeatedly. By using the NOW function and then adding or subtracting a numeric value, we can find out new dates and times easily. In Column C, the resultant values are shown after applying the NOW formula with the addition or subtraction of another numeric value that represents several days.

To add days to the current date, you have to input the number of days only in the following formula:

To go back to a previous date, then you have to simply subtract a number of days from the NOW function and then the function will look like this:

If you use a fraction of a number to add or subtract, then the part of a day will be added or subtracted. For example, you’ll add 1 with the NOW function to find the next day which means you’re adding exactly 24 hours to the current date. But when you use 0.5 instead of 1, then 12 hours will be added with the current date defined by the NOW function.

2. Adding or Subtracting Minutes or Hours with the NOW Function

By adding or subtracting a fraction number with or from the NOW function, you’ll simply find the new time with added minutes or hours. The generic formula to add or subtract a fraction number should look like this:

For the hour_fraction, 1 represents 60 minutes and thus the 30 minutes will be half of it, and the hour fraction, that is 0.5. Similarly, 0.25 and 0.75 denote 15 minutes and 45 minutes respectively. Then you have to make this fraction as a dividend where the divisor will be 24 so that this combined part represents minutes or a fraction of an hour.

To add or subtract a number of hours, you have to input the fraction as a part of 24 hours. So, the generic formula should be:

And you won’t have to divide the fraction with 24 here since you’re already inputting the value as a fraction of 24 hours. So for example, if you add or subtract 0.5 that will denote 12 hours, similarly 0.25 and 0.75 will represent 6 hours and 18 hours respectively.

3. Adding or Subtracting Months with the EDATE and NOW Functions

EDATE function returns the serial number of the date that is the indicated number of months before or after the start date. The syntax of this function is:

=EDATE(start_date, months)

By using EDATE and NOW functions together, you can add or subtract months with or from the current date. In our dataset represented in the picture of this section, Cells C5 and C7 are showing two different dates by adding and subtracting 6 months from the current date. But in this case, the time will not be preserved and it will show 0:00 hours. The generic formula of this combined function can be represented as:

If you want to see the new dates with times preserved, then you have to use the MOD function. This function returns a remainder after a number is divided by a divisor. The syntax of the MOD function is:

=MOD(number, divisor)

So, in our case, the date and time found by the NOW function will be divided by 1 and will then return the remainder with 0 which represents 1/0/1900 but the time will be preserved that will be added to the previous resultant value found by the EDATE function So, the overall generic formula can be shown as:

4. Combining IF and NOW Functions to Find Due Time & Date

Now let’s think about a case where you have to find out if the current date and time exceeds the deadline or not. You’ll have the option to customize the due statuses by using IF and NOW functions. In our dataset in the picture below, Column B represents the deadlines and Column C shows the current date and time. We’ll find out the deadline or due statuses in Column D.

📌 Steps:

➤ In the output Cell D5, the related formula will be:

➤ Now press Enter, autofill the rest of the cells in Column D and you’ll find all the relevant statuses with Due or Deadline Over messages.

5. Showing 1st or Last Date of a Month before or after Current Date by Using NOW and EOMONTH Functions

EOMONTH function returns the serial number of the last day of the month before or after a specified number of months. The syntax of this function is:

=EOMONTH(start_date, months)

By using EOMONTH and NOW functions together, we can get the 1st or last date of the previous or following month from the current date. The generic combined formula of getting the 1st date of a month from the current date is:

Here, the month_serial is the serial number of the month from the current month. For example, to get the 1st date of the current month, we have to use -1 as month_serial, –2 for the previous month and 0 for the next month. Thus we’ll get the last date of the related month and then by adding 1 to the function, we’ll get the 1st day of the next month based on the month_serial of the formula.

So, to get the last date of a month from the current date, the generic formula should look like this:

In this case, the month_serial will be 0 to get the last date of the current month, -1 for the previous month and 1 for the next month.

In the following picture, I hope you’ll have a complete concept of how you should use this combined function to get the 1st or last date of a month from the current date. But unless you use the MOD function here, the time will not be preserved from the current date.

To preserve current time while determining the 1st date of previous or next month from the current date, the generic and combined formula with MOD function will be:

And to get the last date of the previous or next month from the current date while preserving the time, then the formula will be:

The screenshot below should help you more to understand the uses of these functions precisely I hope.

6. Incorporating NOW and TIME Functions to Show Current Time Based on Different Zones

By adding or subtracting the TIME function from the NOW function, we can easily find out the current times based on different zones or areas around the earth. The TIME function converts hours, minutes and seconds given as numbers to an Excel serial number, formatted with a time format. The syntax of the TIME function is:

=TIME(hour, minute, second)

The following picture is an example of finding different zonal times by adding hours and minutes to the current time. Assuming that the current time in Chicago, USA is 1:25:16 PM which has been found by the NOW function. And to get the current time for other different parts of the world we have to add or subtract hours and minutes by inputting them inside the TIME function.

Column C in the picture is showing different times based on zones that have been found by the related NOW and TIME formulas and Column D is showing the formulas that have been used to find these times. So, the generic formula of finding current time for another region or part of the world should be:

7. Counting the Number of Days Left from Current Date by Using DAYS and NOW Functions

DAYS function  shows the number of days between two dates and the syntax of this function is:

=DAYS(end_date, start_date)

By inputting the NOW function as an end_date argument in the DAYS function, we can easily find the number of days left to meet a deadline. The following screenshot is an example of using this formula efficiently.

📌 Steps:

➤ In Cell D5, the related formula will be:

➤ Press Enter, autofill the entire column with Fill Handle and you’ll find the number of days left for each occasion.

8. Determining Final Time to Reach a Destination from Current Time by Using NOW and TIME Functions

Assuming, a car will start traveling from the current time and it’ll cross 360 kilometers at an average speed of 60 kilometers per hour (60 km/h). We’ll determine the final time the car will reach the destination by inputting time as Distance(C5)/Speed(C4) in the hour argument of TIME function.

📌 Steps:

➤ In Cell C7, the related formula will be:

➤ Press Enter and you’ll get the exact destination time at once.

💡 Things to Keep in Mind

🔺 NOW is a volatile function that means it’ll update every time you input or change any data in your Excel spreadsheet. To keep the time static found by the NOW function, you have to copy the time from the cell and then paste it with the VALUES(V)  option. Or if you don’t want to use the NOW function, then you have to press CTRL+SHIFT+; to get the current time and make it static.

🔺 If you want to update the date and time found by the NOW function, you’ll have to press F9 anytime you want to find the updated date and time.

🔺 The date and time format of NOW function is MM/DD/YYYY hh: mm.

Download Practice Workbook

You can download the Excel Workbook that we’ve used to prepare this article.

Concluding Words

I hope all of the methods mentioned above to use the NOW function will now allow you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

<< Go Back to Excel Functions | Learn Excel