NOW Function in Microsoft Excel
Part 1: Introduction
Definition
The NOW function in Microsoft Excel is a date/time function that returns the serial number of the current date and time.
Purpose
The purpose of the NOW function is to provide the current date and time, regardless of when you open the workbook. It is also helpful in calculating values based on the current date and time and updating that value each time you open the worksheet.
Syntax & Arguments
The syntax for the NOW function is as follows:
NOW() The NOW function syntax has no arguments.
Return Value
The NOW function returns the serial number of the current date and time. The serial number is the date-time code used by Excel for date and time calculations.
Remarks
Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,447 days after January 1, 1900. Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
Part 2: Examples
Let’s look at some examples of using the NOW function in business.
Example 1
Purpose: To display the current date and time in a cell.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Date and Time |
| 2 | =NOW() |
Explanation: In this example, we want to display the current date and time in a cell. The formula =NOW() returns the current date and time.
Example 2
Purpose: To calculate the date and time 12 hours ago.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date and Time 12 Hours Ago |
| 2 | =NOW()-0.5 |
Explanation: In this example, we want to calculate the date and time of 12 hours ago. The formula =NOW()-0.5 returns the date and time 12 hours ago.
Example 3
Purpose: To calculate the date and time 7 days in the future.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date and Time 7 Days in the Future |
| 2 | =NOW()+7 |
Explanation: In this example, we want to calculate the date and time of 7 days in the future. The formula =NOW()+7 returns the date and time 7 days in the future.
Example 4
Purpose: To calculate the date and time 2 days and 6 hours ago.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date and Time 2 Days and 6 Hours Ago |
| 2 | =NOW()-2.25 |
Explanation: In this example, we want to calculate the date and time of 2 days and 6 hours ago. The formula =NOW()-2.25 returns the date and time 2 days and 6 hours ago.
Example 5
Purpose: To calculate the date and time 1 hour in the future.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date and Time 1 Hour in the Future |
| 2 | =NOW()+1/24 |
Explanation: In this example, we want to calculate the date and time 1 hour in the future. The formula =NOW()+1/24 returns the date and time 1 hour in the future.
Example 6: Using NOW with HOUR Function
Purpose: To extract the current hour from the current date and time.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Hour |
| 2 | =HOUR(NOW()) |
Explanation: In this example, we want to extract the current hour from the current date and time. The HOUR function combined with the NOW function helps to remove the present hour.
Example 7: Using NOW with the MINUTE Function
Purpose: To extract the current minute from the current date and time.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Minute |
| 2 | =MINUTE(NOW()) |
Explanation: In this example, we want to extract the current minute from the current date and time. The MINUTE function combined with the NOW function helps to remove the present minute.
Example 8: Using NOW with SECOND Function
Purpose: To extract the current second from the current date and time.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Second |
| 2 | =SECOND(NOW()) |
Explanation: In this example, we want to extract the current second from the current date and time. The SECOND function combined with the NOW function helps to remove the current second.
Example 9: Using NOW with the TEXT Function
Purpose: To format the current date and time in a specific format.
Data tables and formulas:
| A | |
|---|---|
| 1 | Formatted Date and Time |
| 2 | =TEXT(NOW(), “mmmm dd, yyyy hh:mm: ss AM/PM”) |
Explanation: In this example, we want to format the current date and time in a specific format. The TEXT function combined with the NOW function helps to format the current date and time.
Example 10: Using NOW with INT Function
Purpose: To extract the current date from the current date and time.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Date |
| 2 | =INT(NOW()) |
Explanation: In this example, we want to extract the current date from the current date and time. The INT function combined with the NOW function helps to remove the current date.
Example 11: Using NOW with MOD Function
Purpose: To extract the current time from the current date and time.
Data tables and formulas:
| A | |
|---|---|
| 1 | Current Time |
| 2 | =MOD(NOW(), 1) |
Explanation: In this example, we want to extract the current time from the current date and time. The MOD function combined with the NOW function helps to remove the present time.
Example 12: Using NOW with IF Function
Purpose: To classify whether the current time is AM or PM.
Data tables and formulas:
| A | |
|---|---|
| 1 | AM or PM |
| 2 | =IF(HOUR(NOW())<12, “AM”, “PM”) |
Explanation: In this example, we want to classify whether the current time is AM or PM. The IF function combined with the NOW function helps to organize the present time.
Example 13: Using NOW with WEEKDAY Function
Purpose: To find the day of the week for the current date.
Data tables and formulas:
| A | |
|---|---|
| 1 | Day of Week |
| 2 | =WEEKDAY(NOW()) |
Explanation: In this example, we want to find the day of the week for the current date. The WEEKDAY function combined with the NOW function helps to see the day of the week.
Part 3: Tips and Tricks
The NOW function can be combined to create more complex formulas. For example, you can use it with the YEAR, MONTH, and DAY functions to extract the year, month, and day from the current date and time.
Remember that Excel stores date as sequential serial numbers so that you can use these numbers in calculations.
The NOW function does not have any arguments and does not require any input.
The NOW function will update the date and time when the worksheet is recalculated.
If the NOW function does not update the date and time when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. On the File tab, click Options, and then in the Formulas category under Calculation options, make sure that Automatic is selected.