How to Highlight Excel Dates Through 7:00 PM with Conditional Formatting
Question details
The user wants to apply conditional formatting to highlight cells containing date and time values up to 7:00 PM of the current day, instead of using the standard midnight cutoff.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying specific time-based highlighting rules in a spreadsheet to track daily deadlines or schedules up to a certain hour.
- Observed behavior
- By default, conditional formatting rules for 'Today' use midnight as the cutoff. The goal is to extend or specify the exact cutoff time as 7:00 PM.
Ensure your target cells are properly formatted as 'Date and Time' or 'Custom' so Excel can correctly read and calculate the underlying numeric values of the times.
Use TODAY and TIME Functions (For Today and Earlier)
Apply a custom formula rule to highlight all date and time values that fall on or before 7:00 PM of the current day.
This formula uses the TODAY() function to get the current date and adds the TIME() function to set the exact 7:00 PM cutoff.
Highlight the range of cells containing the date and time data you want to format (e.g., A1:A100). Note the top-left cell of your selection (e.g., A1).
Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
In the New Formatting Rule dialog, click on 'Use a formula to determine which cells to format'.
In the formula box, type =A1<=TODAY()+TIME(19,0,0). Replace 'A1' with the actual top-left cell of your selected range.
Click the 'Format' button, choose your preferred fill color or font style under the respective tabs, and click 'OK' twice to apply the rule.
Use INT and MOD Functions (Strictly for Today Before 7 PM)
Use this alternative formula if you only want to highlight times strictly from today's date before 7:00 PM, explicitly excluding any previous days.
Master Conditional Formatting with WPS Spreadsheet
Easily apply complex custom formulas, highlight specific date and time rules, and manage your data efficiently using WPS Spreadsheet. It provides a highly compatible and intuitive interface for all your data analysis needs.
- 1. Open your document: Launch WPS Office and open the spreadsheet file containing your date and time data.
- 2. Access Conditional Formatting: Select your data range, click the 'Home' tab, and choose 'Conditional Formatting' > 'New Rule'.
- 3. Apply custom formulas: Select 'Use a formula', input your custom time-based formula (like the TODAY and TIME combination), pick a highlight color, and click 'OK'.
Frequently Asked Questions
Why is my conditional formatting formula highlighting the wrong cells?
This usually happens if the cell reference in your formula doesn't match the top-left cell of your selected range. Ensure that if you select B2:B50, your formula explicitly uses B2 instead of A1.
How does the TIME(19,0,0) function work in this formula?
The TIME function in spreadsheet software creates a specific time value. The arguments are hours, minutes, and seconds. TIME(19,0,0) represents 19:00:00 in 24-hour format, which corresponds to exactly 7:00 PM.
Can I highlight dates up to a different time, like 5:00 PM?
Yes, you can easily adjust the formula. Just change the TIME function arguments to TIME(17,0,0) for 5:00 PM, or TIME(14,30,0) for 2:30 PM.
Does this conditional formatting rule update automatically tomorrow?
Yes. Because the formula uses the TODAY() function, the highlighted cells will dynamically update based on your computer's current system date and time.
