# How to flag dates that expire within 30 days in Excel

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

**Excel:** `=IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK"))` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Certification | Employee | Expires | Status | Today | 2026-10-29 |
| Forklift | R. Diaz | 2026-10-15 |  | Warn within (days) | 30 |
| First aid | M. Lee | 2026-11-20 |  |  |  |
| CPR | A. Khan | 2027-03-01 |  |  |  |
| OSHA 10 | J. Webb | 2026-11-28 |  |  |  |
| Hazmat | T. Nguyen | 2026-10-29 |  |  |  |
| Food safety | P. Rossi | 2026-11-29 |  |  |  |

Formula in D2, filled to D7: `=IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK"))`

Result: Expired; Expires soon; OK; Expires soon; Expires soon; OK

## 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.

## Other ways

- `=IFS(C2<$F$1,"Expired",C2<=$F$1+$F$2,"Expires soon",TRUE,"OK")`: The same with IFS, which is easier to extend with more levels.
- `=IF(C2-$F$1<=$F$2,"Renew","")`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/flag-dates-expiring-within-30-days