Excel NOW Function: Real-Time Date & Time in Your Sheets | HackerNoon

The NOW function in Excel returns the current date and time from your system clock.

NOW Function: A Brief

The NOW function in Excel returns the current date and time based on your computer’s system clock. The NOW function will update every time the worksheet recalculates or when the file is opened. It can be useful for the following scenarios:

• Logging timestamps
• Tracking task updates
• Calculating durations or deadlines from the present moment.

NOW Function: A Syntax

=NOW() // Returns the current date and time from your device clock.

NOW Function: Introduced In

The NOW function was introduced in Excel 1.0 and is a built-in function. If you want to recreate the working of the NOW function using formulas, you will need to manually combine functions as shown below.

=TODAY() + (HOUR(NOW())/24 + MINUTE(NOW())/1440)

By combining functions like TODAY, HOUR, NOW, and MINUTE with mathematical operations, you can get the current date and time based on your system clock. However, this type of formula is static. Once it returns a result, it does not update automatically every time you reopen the file, unlike the NOW function, which refreshes with the current date and time whenever the sheet is opened or recalculated.

By default, a formula may show only the date or only the time in a cell. To display both date and time, right-click the cell and choose the Format Cells option. In the Number tab, choose Custom, then in the Type field, select the dd-mm-yyyy hh:mm, and click the OK button.

Now, the content in the cell will be updated with the date and time value as shown in the image below.

Formatting cells will display both date and time.

Examples of Using NOW Function With Other Nested Functions:

In the example below, I’ve included 13 different scenarios showing how to use the NOW function together with other nested functions. Each case includes the formula, the output, and a brief explanation for clarity.

Example’s Explanation:

• Case 1: The formula checks whether the current time is before noon or afternoon. If HOUR(NOW()) is less than 12, it returns “Morning” or otherwise, it shows “Afternoon” as the output value.
• Case 2: EOMONTH returns the last day of the current month. By subtracting TODAY(), the formula shows how many days are left in the month. Based on the current device’s clock, it may return 6 days remaining.
• Case 3: The formula TEXT(NOW(), “hh:mm:ss”) displays only the current time, removing the date portion and showing it in HH:MM:SS format.
• Case 4: It uses the WEEKDAY function to check if today is a weekday or weekend. If the result is 6 or 7, it returns “Weekend” or otherwise, it returns “Weekday” as the output.
• Case 5: This formula calculates total hours passed since midnight using HOUR(NOW()) + MINUTE(NOW()) / 60. The result is shown as a decimal, such as 12.38.
• Case 6: It calculates how many hours are left in the day by subtracting the total passed hours from 24.
• Case 7: This checks if the current time is within typical office hours (9 AM to 5 PM) using IF and AND with HOUR(NOW()). If true, it returns “Yes” as the output.
• Case 8: The formula adds 15 minutes to the current time using NOW() + TIME(0,15,0), useful for setting reminders or future triggers.
• Case 9: It subtracts 2 hours from the current time with NOW() – TIME(2,0,0), showing a time 2 hours earlier.
• Case 10: TEXT(NOW(), “dddd”) returns the full name of the day, like “Thursday“, which is useful in dynamic headers or reports.
• Case 11: FLOOR(NOW(), “1:00”) rounds the current time down to the nearest hour, removing minutes and seconds.
• Case 12: The formula checks whether today is a Monday using WEEKDAY(NOW(), 2). If the result is 1, it returns “Yes”.
• Case 13: SECOND(NOW()) extracts the current second value from the time.

Functions Used in the Examples:

That’s it. This tutorial was originally published on How to Use Excel NOW Function?