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.
We explored the EDATE function and DATEDIF in other blog articles. This time we’ll take a look at the NOW Function.
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.
| Current | Formula | New Date / Time |
|---|---|---|
| 7/25/2022 10:48 | =NOW()+1 | 7/26/2022 10:48 |
| 7/25/2022 10:48 | =NOW()-7 | 7/18/2022 10:48 |
| 7/25/2022 10:48 | =NOW()+365 | 7/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.
| Current | Formula | New Date / Time |
|---|---|---|
| 7/25/2022 10:48 | =NOW()+1/24 | 7/25/2022 11:48 |
| 7/25/2022 10:48 | =NOW()+0.75/24 | 7/18/2022 11:33 |
| 7/25/2022 10:48 | =NOW()-2/60/24 | 7/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.
What’s Next?
The NOW function is just one of over 200 functions available in Excel.
If you want learn about other Excel functions, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. These functions are incredibly useful for anyone who needs to combine data from multiple sources.
See you next time!
