Formula Errors

How to Highlight Rows After a Midnight Due Time in Excel

Algirdas Jasaitis Sep 29, 2026 869 views

Question details

The user needs a method to calculate a same-day 11:59 PM deadline based on an admission date, and then highlight the entire row automatically once the current time passes that deadline.

How to Highlight Rows After a Midnight Due Time in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking tasks or admissions where items must be completed by the end of the same day, requiring visual flags for overdue items.
Observed behavior: A dynamic formula is required to calculate the deadline alongside a conditional formatting rule to evaluate the current time against that deadline.

Before you start

Ensure your admission dates and times are stored as valid numeric date/time values in Excel, rather than plain text strings, so the formulas can calculate them correctly.

Solution 1Recommended

Use Date Functions and Conditional Formatting to Highlight Overdue Rows

This solution calculates the 11:59 PM deadline using the DATE and TIME functions, then applies a conditional formatting rule using the NOW function to highlight rows when the deadline passes.

By separating the deadline calculation into its own column, you keep your data organized and easy to audit. The conditional formatting rule will then check the current system time against this calculated deadline to apply the highlight.

Calculate the 11:59 PM Deadline

Assuming your admission date and time is in cell C2, select cell D2 and enter the formula: =IF(C2<>"",DATE(YEAR(C2),MONTH(C2),DAY(C2))+TIME(23,59,0),""). This creates a deadline of 11:59 PM for the date provided in C2.

Format the Deadline Cell

Right-click cell D2 and choose 'Format Cells'. Go to the 'Number' tab, select the 'Custom' category, and enter the format 'm/d/yyyy h:mm' to display the date and time properly.

Set Up Conditional Formatting

Select the entire range of rows you want to highlight. Navigate to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.

Apply the Highlighting Formula

Choose 'Use a formula to determine which cells to format'. Enter the formula =AND($C2<>"",NOW()>$D2). Click 'Format' to pick a fill color (like red), then click 'OK' twice to apply the rule.

Use Date Functions and Conditional Formatting to Highlight Overdue Rows

Automatic Recalculation: The NOW() function updates automatically whenever the worksheet recalculates, ensuring your overdue rows are highlighted dynamically as time passes.

Manage Deadlines Efficiently

Highlight Overdue Deadlines Easily in WPS Spreadsheet

WPS Office offers robust conditional formatting and advanced date/time functions, making it incredibly simple to track, manage, and highlight deadlines exactly as you would in Excel.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing your admission dates.
2. Create the Deadline Column: Input your DATE and TIME calculation formula into the deadline column just as you would in Excel.
3. Apply the Rule: Select your data range, click 'Conditional Formatting' under the Home tab, select 'New Rule', and input your formula to apply the highlight.

Fully compatible with Microsoft Excel conditional formatting rules and date/time functions.Intuitive and clean interface for managing complex spreadsheet formulas.Free, lightweight, and fast alternative for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the NOW() function not highlighting rows exactly at midnight?

The NOW() function updates when the workbook recalculates. If the workbook has been sitting idle, you can press F9 on your keyboard to manually force a calculation and update the conditional formatting immediately.

How can I set the deadline to exactly 12:00 AM the next day instead of 11:59 PM?

Instead of adding TIME(23,59,0), you can simply add 1 to the date portion of your formula to represent the start of the following day. Use this formula: =IF(C2<>"",DATE(YEAR(C2),MONTH(C2),DAY(C2))+1,"").

Why is my conditional formatting applying to the wrong rows or cells?

This usually happens due to incorrect cell references. Ensure you use mixed references like $C2 and $D2 in your conditional formatting rule. The dollar sign locks the column but allows the row number to adjust, ensuring the entire row highlights correctly based on its specific deadline.

Excel & Data Problems