How to Automatically Highlight Expiring Dates in Google Sheets (Step-by-Step)

Two years ago, I accidentally paid $450 for a software subscription renewal I had planned to cancel months earlier. The expiration date was sitting right there in cell E14 of my tracking sheet. But because it looked identical to 200 other rows of plain black text, my eyes skimmed right past it.

That expensive blunder taught me a lesson: raw data doesn't trigger action. Visual cues do.

If you manage domain names, client contracts, warranty periods, or project deadlines, relying on your memory is a recipe for disaster. Google Sheets can flag upcoming deadlines long before they turn into costly mistakes. Let me walk you through the exact setup I use to make cells change color automatically as expiration dates approach.

Step 1: Prepare Your Date Columns

Before writing formulas, we need to ensure Google Sheets recognizes your entries as actual dates, not plain text.

My colleague Sarah ran into an issue last month where her formatting rules completely failed. We discovered half her dates were typed as 10/12/2025 while others were written as Oct 12, 2025. Sheets got confused and ignored half her rows.

Here is how to clean up your data first:

1. Highlight the column containing your expiration dates (for example, Column B).
2. Click Format in the top menu bar.
3. Hover over Number and select Date.

Now every cell in that column speaks the exact same language.

Step 2: Highlight Dates Expiring Within 30 Days

Let's start with a warning system. We want cells to turn soft yellow when an item is within 30 days of expiring.

We will use conditional formatting combined with the TODAY() function. This built-in function checks the current calendar date every time you open the spreadsheet.

Setting Up the 30-Day Alert Rule

1. Select your date range (e.g., B2:B100). Avoid selecting the header in row 1.
2. Go to Format > Conditional formatting.
3. Under the Format rules tab, open the dropdown under Format cells if... and choose Custom formula is.
4. Type this exact formula in the box: =AND(B2<>"", B2-TODAY()<=30, B2-TODAY()>=0)
5. Under Formatting style, set the fill color to light yellow.
6. Click Done.

Why does this formula work so well? The B2<>"" part tells Sheets to ignore blank cells. Without it, every empty row in your spreadsheet will glow yellow, which gets annoying fast.

Pro Tip: Keep your color palette soft. Bright fluorescent colors cause visual fatigue when you are staring at a sheet for an hour. I prefer pastel yellow for warnings and muted pink for expired items.

Step 3: Highlight Dates That Are Already Expired

Now let's add a second rule to flag items that have already passed their expiration date. This requires a bright visual indicator so you can take immediate action.

1. Keep your range (B2:B100) selected in the Conditional Formatting panel.
2. Click Add another rule.
3. Choose Custom formula is from the dropdown menu.
4. Enter the following formula: =AND(B2<>"", B2<TODAY())
5. Set the fill color to soft red or light pink with dark red text.
6. Click Done.

Ordering Your Rules Correctly

Google Sheets evaluates conditional formatting rules from top to bottom. Order matters.

If your 30-day warning rule sits above your expired rule, expired dates might display as yellow instead of red. In the conditional formatting side panel, hover over the rule card until you see a grip icon (six dots), then drag the Expired Rule (Red) to the top of the list.

Step 4: Create a Multi-Tiered Urgency System

If you manage high-stakes deadlines, a single 30-day window might not give you enough granularity. I like using a three-tier traffic light system on my operational sheets:

• Expired: Past due (Red)
• Urgent: 7 days or fewer remaining (Orange)
• Warning: 8 to 30 days remaining (Yellow)

Here are the formulas you need for this advanced setup:

1. For Expired items (Red): =AND(B2<>"", B2<TODAY())
2. For Critical 7-Day window (Orange): =AND(B2<>"", B2-TODAY()<=7, B2-TODAY()>=0)
3. For 30-Day Heads-Up (Yellow): =AND(B2<>"", B2-TODAY()<=30, B2-TODAY()>7)

Stack these rules in your panel in that exact order: Red at the top, Orange in the middle, Yellow at the bottom. The result is a clean dashboard that instantly directs your focus where it is needed most.

Note on Performance: If your spreadsheet contains tens of thousands of rows, custom conditional formatting formulas can occasionally slow down sheet loading times. For standard tracking sheets with under 5,000 rows, performance remains snappy.

Troubleshooting Common Formula Glitches

Sometimes you set up everything right, but the colors refuse to show up. Here are three quick fixes I frequently share when readers email me for help:

• The Entire Column Turns Red: You likely forgot the B2<>"" check, or your rule range starts at B1 while your formula references B2. Ensure the formula reference matches the very first cell in your selected range.
• The Colors Are Offset By One Row: If your selection is B2:B100, your formula must start with B2. If you accidentally type =B1<TODAY(), the formatting will highlight the row above the actual target date.
• Dates Are Ignored Completely: Check if your dates are secretly stored as text. Type =ISNUMBER(B2) in a blank cell. If it returns FALSE, your date entry is formatted as text. Select the column and apply Format > Number > Date again.

FAQ: Expiration Highlighting in Google Sheets

Why are blank cells getting highlighted even with custom formulas?

Blank cells in Google Sheets evaluate to zero in numeric math operations, which causes TODAY() - 0 to trigger date checks. Adding B2<>"" inside an AND() function explicitly skips empty cells.

Does the TODAY() formula update automatically every day?

Yes, but with a small caveat. TODAY() recalculates whenever you open or edit the sheet. If you leave a tab open on your browser for weeks without refreshing, go to File > Settings > Calculation and set recalculation to On change and every hour.

Can I highlight the entire row instead of just the date cell?

Absolutely. Select the full table range (e.g., A2:E100) instead of just column B. Then update your formula to lock the date column with a dollar sign: =$B2<TODAY(). The dollar sign forces Google Sheets to check Column B for every cell across that row.

Wrapping Up

Setting up automatic color-coding takes about five minutes, but it has saved me hundreds of dollars and countless headaches over the past two years. You don't need complex Apps Script code or paid add-ons—just simple, smart conditional formatting rules.

Try adding the 30-day warning rule to your primary tracking sheet today. Drop a comment below if you run into any formula hiccups, and I will be happy to help you troubleshoot!