How to Highlight Expired Inventory Dates in Excel Fast

Select your date column. Navigate to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Assuming your first date is in cell C2, paste this exact formula:

Excel

=AND(C2<>"", C2<TODAY())

Click Format, choose a red fill color, and click OK.

The Syntax Breakdown

AND(...): This function allows us to check for multiple conditions at once. Both conditions must be true for the formatting to trigger. This prevents blank cells from turning red.
C2<>"": Condition 1. This checks that cell C2 is not (<>) empty ("").
C2: The relative cell reference to the top cell in your date column. Because it lacks absolute locking symbols ($), Excel will dynamically apply this check row by row down the entire column.
<: The standard mathematical operator for “less than,” meaning chronological dates that occurred before our comparison point.
TODAY(): Condition 2. A volatile Excel function that constantly updates to the current system date. When paired with the < operator, it strictly isolates dates in the past.

Real-World Example: Tracking Expired Medical Supplies

You manage a regional hospital warehouse tracking surgical supplies with strict shelf lives. You need an automated visual cue when a batch of supplies passes its expiration date so it can be safely discarded.

Here is your raw data structure:

Row	A (SKU)	B (Item Description)	C (Expiration Date)
2	MED-014	Saline Solution 500ml	10/12/2023
3	MED-088	N95 Respirator Masks	08/15/2028
4	MED-112	Suture Kits (Absorbable)	03/01/2024
5	MED-156	Sterile Syringes 10cc

How to apply:

Highlight the data range in column C, starting from C2 down to C5.
Go to Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter =AND(C2<>"", C2<TODAY()).
Set the format to a red background with white text.

The Output:

C2 will highlight Red (10/12/2023 is in the past).
C3 will remain unformatted (08/15/2028 is in the future).
C4 will highlight Red (03/01/2024 is in the past).
C5 will remain unformatted (It is blank, and the C2<>"" logic caught it).

Common Errors & How to Fix Them

Every blank cell in the column turned red: Excel treats completely blank cells as the value 0 (January 1, 1900), which is always less than TODAY(). Fix: Wrap your initial rule in an AND function to exclude blanks: =AND(C2<>"", C2<TODAY()).
The highlighting is offset or affecting the wrong rows: You selected the data range starting at Row 2, but wrote the formula referencing Row 1 (e.g., C1<TODAY()). Fix: Open Conditional Formatting > Manage Rules and ensure the cell reference in your formula exactly matches the top-left cell of the “Applies to” range.
Past dates are not highlighting at all: Your system exported the dates as text strings, not actual serial date values, rendering mathematical functions useless. Fix: Highlight the date column, click Data > Text to Columns, and immediately click Finish to force Excel to convert the text back into recognized dates.