Categories
Microsoft Excel

Use the NOW Function in Excel to Work with Dates and Times

Participants I meet in Microsoft Excel training often tell me that they find working with dates tricky. The good news is there’s an entire category of Excel functions designed to work with dates and times.

The NOW function in Excel returns a serial number that represents the current date and time. The NOW function can display the current date and time or it can be used in calculations that display other date and time values.

If a cell is formatted using General when you enter the NOW function, Excel updates it to match the date and time format specified in regional settings.

When you use the NOW function, it always returns the current date and time. The results are dynamic.

NOW Function Syntax

The NOW function in Excel has no arguments. The syntax is:

=NOW()

In the following example, the NOW function was entered into two cells. Excel automatically formatted cell D1 with the default date and time format (m/d/yyyy h:mm). I reset the formatting in cell D2 to General to show you the serial number that Excel uses for calculations.

NOW Function Examples

The NOW function in Excel can be used to display the current date and time in a spreadsheet. It can also be used to perform calculations data that incorporates dates and times.

Let’s look at a few examples to help you understand how to use the NOW function.

Display the Current Date and Time with the NOW Function

To display the current date and time, you simply type =NOW() then press Enter.

If a cell is formatted using General when you enter the NOW function, Excel updates it to match the date and time format specified in regional settings.

Adding and Subtracting Days with the NOW Function

If you need to add or subtract days, the process is fairly straightforward. You would simply use the formula:

=NOW() ± number_of_days

The following table contains a few examples.

CurrentFormulaNew Date / Time
7/25/2022 10:48=NOW()+17/26/2022 10:48
7/25/2022 10:48=NOW()-77/18/2022 10:48
7/25/2022 10:48=NOW()+3657/25/2023 10:48

Adding and Subtracting Hours or Minutes with the NOW Function

To add and subtract hours or minutes using the Excel NOW function, you need to incorporate a formula that converts hours or minutes to decimal values.

Here are a few examples.

CurrentFormulaNew Date / Time
7/25/2022 10:48=NOW()+1/247/25/2022 11:48
7/25/2022 10:48=NOW()+0.75/247/18/2022 11:33
7/25/2022 10:48=NOW()-2/60/247/25/2023 10:46

Example 1 converts 1 hour to a decimal by dividing 1 hour by 24. =NOW()+1/24 will add 1 hour to the current time value.

Example 2 (=NOW()+0.75/24) adds 45 minutes, or .75 hours, to the current time.

Example 3 subtracts 2 minutes from the current time (=NOW()-2/60/24). The formula first converts 2 minutes to a decimal (2/60) then converts that value to hours.

How Will You Use the Excel NOW Function?

The NOW function in Excel is useful any time you need perform calculations based on data that references the current date and time.

Remember: the Excel NOW function is volatile. It returns the current date and time, according to your computers internal calendar and clock.

If you need to enter a static date or time, you can type those values directly into a cell. You can also press Ctrl + ; to enter the current date, press Ctrl + Shift + : to add the current time.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.