How to Highlight Overdue Dates in Excel Using Conditional Formatting
Open an Excel workbook (.xlsx) in Cue Office and use a custom conditional formatting formula to check the due date and checkout status together. Highlight a row when its due date is earlier than the check date and its status is still Out.
Set up a rule based on due date and checkout status
In this community equipment loan sheet, column B contains the due dates, and column C uses Out to indicate checked-out items and Returned for returned items. Enter June 15, 2027 as the check date in F2. Use date values in F2 and for any due dates you enter in column B.
Select A2:C6, then click Conditional Formatting on the Home tab. In the side panel, click the plus sign next to Rules, confirm that Apply Range is A2:C6, and set Style Type to Custom Formula.
In the Style Rule field, enter the formula below, keeping the leading equals sign. If the input field already contains an equals sign, enter only the rest of the formula. Keep the light blue fill, choose black from the font palette, and click Submit. Switch back to the sheet to ensure the text remains easy to read. Once the rule is saved, the expression in the list will omit the leading equals sign.
=AND($B2<>"",$B2<$F$2,$C2="Out")
$B2<>"" excludes blank dates, $B2<$F$2 matches only records earlier than the check date, and $C2="Out" requires the equipment to still be checked out. $B2 and $C2 lock the columns while allowing row numbers to change with each record. $F$2 locks the check date reference to that specific cell. When applying this to your own sheet, make sure the row numbers in the formula match the first row of your applied range.
Test the rule by changing the check date and status
Save the workbook using File → Save As…, then close and reopen it. With a check date of June 15, only the Projector due on June 12 is both checked out and overdue. The Speaker due the same day, the Camera due on June 18, the returned Tripod, and the Cable with no due date remain unhighlighted.
In cell F2 of your test copy, temporarily enter =DATE(2027,6,19). On June 19, the projector, speaker, and camera are all overdue and still out, so all three rows are highlighted. The Tripod and Cable remain unhighlighted.
Change F2 back to June 15, then temporarily set the projector’s status in C2 to Returned. Row 2 loses its fill. After checking, restore C2 to Out.
Use Cue AI to apply the conditional formatting
Start with a copy of the loan sheet before adding any rules. Tell Cue AI the range to format, where to find the check date, which status to check, and which rows to leave alone.
At the loan desk, I want to spot equipment that should already have come back. Add a conditional formatting rule to A2 through C6 that gives the whole row a light fill only when the due date is earlier than the check date in F2 and the status is Out. Leave rows with a blank due date, due today, due later, or already Returned unmarked. Keep the original data and apply the rule so the color changes when dates or statuses change.
Here, Cue AI applied the dynamic rule across A2:C6. When we changed the projector status to Returned, the red fill disappeared instantly. After restoring Out, saving, and reopening, only the projector was highlighted, with all original data intact.
Practice with five equipment checkout records
Expand complete example
Paste the tab-delimited data below starting at cell A1. Leave columns D and E blank, and keep the check date in F2. After applying the rule to A2:C6, only the row for the projector should be highlighted.
Item Due date Status Check date Projector 6/12/2027 Out 6/15/2027 Speaker 6/15/2027 Out Camera 6/18/2027 Out Tripod 6/13/2027 Returned Cable Out
Hand off the overdue items to the next shift
Before handing over the loan desk, update the check date in F2. Ask Cue AI for a list of the equipment that is overdue and still out, with each item’s original due date. Check the list against the sheet before sending it to the next shift. With June 15 in F2, the projector belongs on the list. The speaker is due that day, so check it again at the next handover.
Use Excel (.xlsx) to track replies and Word (.docx) to tell the next shift who to contact. When training new volunteers, you can compare the projector and speaker in PowerPoint (.pptx) to explain the difference between overdue items and items due today. Cue Office opens and edits all three file formats for free. If you export a reminder list as a PDF, you can verify dates and item names with the free PDF reader. Download Cue Office for free