How to flag dates that expire within 30 days in Excel

Q: How do I highlight them with conditional formatting?

Use the rule =AND($C2>=TODAY(),$C2<=TODAY()+30) for expiring soon and =$C2<TODAY() for expired.

Q: How do I count how many expire in the next 30 days?

=COUNTIFS(C2:C7,">="&F1,C2:C7,"<="&F1+F2).

Formula (Excel and Google Sheets)

=IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK"))

Test for expired first, then for the warning window: =IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK")).

Result on the example: one result per row in D2, filled down to D7
Works in: Excel 2019 and later, including Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.

How it works

The first test catches anything already past the date in F1. For the rest, C2-$F$1 is the days left, and the second test flags those within the warning window in F2. The OSHA 10 card expires in exactly 30 days, so it is flagged; Food safety, at 31 days, is not.

=IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK"))

Result"Expired"

C210/15/2026
The value in C2.
$F$110/29/2026
The value in F1.
C2<$F$1TRUE
The < operator tests less than.
C2-$F$1-14
The - operator subtracts.
$F$230
The value in F2.
C2-$F$1<=$F$2TRUE
The <= operator tests at most.
IF(C2-$F$1<=$F$2,"Expires soon","OK")"Expires soon"not used
IF: Returns one value if a test is true, another if false.
- logical_testC2-$F$1<=$F$2TRUE
- value_if_true"Expires soon"
- value_if_false"OK"not used
IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK"))"Expired"
IF: Returns one value if a test is true, another if false.
- logical_testC2<$F$1TRUE
- value_if_true"Expired"
- value_if_falseIF(C2-$F$1<=$F$2,"Expires soon","OK")"Expires soon"not used

In Google Sheets

The same formula works in Google Sheets: =IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK")). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IFS(C2<$F$1,"Expired",C2<=$F$1+$F$2,"Expires soon",TRUE,"OK")	Expired, Expires soon, OK…	The same with IFS, which is easier to extend with more levels.
=IF(C2-$F$1<=$F$2,"Renew","")	Renew, Renew, …Different result	One flag for anything expired or expiring. Expired and expiring both show Renew.

Common mistakes

Test for expired first. Past dates have negative days left, so they also pass <=30 and would show "Expires soon".
Keep today in a cell. TODAY() inside the formula works for a live sheet, but an old report can't be reproduced.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Flag dates that expire within 30 days

In D2, show "Expired" if the date is before today in F1, "Expires soon" if it is within the days in F2, and "OK" otherwise. Fill down to D7.

Questions

How do I highlight them with conditional formatting?

Use the rule =AND($C2>=TODAY(),$C2<=TODAY()+30) for expiring soon and =$C2<TODAY() for expired.

How do I count how many expire in the next 30 days?

=COUNTIFS(C2:C7,">="&F1,C2:C7,"<="&F1+F2).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.