Calculation Issues

Fix Excel NOW and IF Functions Returning Wrong Results

Amos Gikunda Oct 16, 2026 869 views

Question details

The user needs to understand why an IF formula comparing the NOW() function to a time value incorrectly evaluates to TRUE.

Fix Excel NOW and IF Functions Returning Wrong Results

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing the current time against a specific time value (like noon) stored in another cell using logical IF formulas.
Observed behavior: The IF function returns unexpected results because NOW() generates a full date-time serial number, while the reference cell only contains a fractional time value.

Before you start

Before modifying your formula, temporarily change the formatting of your reference cell to 'General' to verify it displays as a decimal (e.g., 0.5 for noon). If it still looks like text (e.g., '12:00 PM'), it is formatted incorrectly.

Solution 1Recommended

Isolate the time using the HOUR or MOD function

Extracting only the time or hour from the NOW() function ensures you are comparing identical data types.

Excel stores dates as whole numbers and times as decimals. Since the NOW() function includes both the date and time, comparing it directly to a time-only cell (which is just a decimal) will always result in NOW() being greater. You must strip the date portion from NOW() to perform an accurate logical comparison.

Compare exact hours

To compare the current hour as a whole number (0-23), replace NOW() in your formula with HOUR(NOW()). For example, type: =IF(HOUR(NOW())>12, "Afternoon", "Morning").

Compare exact times

To compare specific minutes and seconds against your reference cell (e.g., C6), use the MOD function to remove the date integer. Update your formula to: =IF(MOD(NOW(),1)>C6, "Past Deadline", "On Time").

Isolate the time using the HOUR or MOD function

Understanding Serial Numbers: The formula =MOD(NOW(),1) works by dividing the full date-time serial number by 1. It returns only the decimal remainder, which cleanly isolates the time of day.

Solution 2

Verify and correct the reference cell data type

Formulas often fail if the target reference cell contains text instead of a recognized calculable time format.

Advanced Data Calculation

Accurately Calculate Time and Dates with WPS Spreadsheet

WPS Spreadsheet makes it incredibly easy to manage date and time data without formula errors. It fully supports standard time functions, allowing you to build complex logical statements and compare serial numbers accurately with a familiar interface.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing your time comparison formulas.
2. Apply time functions: Click the target cell and enter your IF function combining HOUR(NOW()) or MOD(NOW(),1) to compare the time accurately.
3. Format reference cells: Right-click your reference cell, select 'Format Cells', and choose 'Time' from the list to ensure the data is calculable.
4. Calculate results: Press Enter to execute the formula, and then drag the fill handle down to apply the corrected logic to other rows.

100% compatible with Microsoft Excel date and time functions, including NOW(), IF(), and HOUR().Easily format cells to visualize decimal values as standard times with built-in tools.A completely free and lightweight alternative for all your daily spreadsheet analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

How does Excel store dates and times internally?

Excel stores dates and times as serial numbers. The integer portion represents the days since January 1, 1900, while the decimal portion represents the fractional time of a 24-hour day (e.g., 0.5 is exactly 12:00 PM).

What is the difference between the TODAY() and NOW() functions?

The TODAY() function returns only the current date as a whole integer. In contrast, NOW() returns both the current date and the exact current time, meaning it produces an integer along with a decimal.

Why does my IF formula treat a cell containing time as text?

If time data was imported from another system or typed with an unrecognized structure, the spreadsheet application might interpret it as plain text. You can use the TIMEVALUE() function to force the text string back into a calculable decimal number.

Excel & Data Problems