# How to flag dates that are due in the next 30 days in Excel

Test that the date is on or after today and no later than today plus the window: =IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon","").

**Excel:** `=IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon","")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Contract | Renews on | Flag |  | As of | 2026-10-29 |
| Acme | 2026-11-03 |  |  | Days ahead | 30 |
| Birch | 2026-12-15 |  |  |  |  |
| Cobalt | 2026-10-20 |  |  |  |  |
| Delta | 2026-11-28 |  |  |  |  |
| Ember | 2027-01-10 |  |  |  |  |
| Fjord | 2026-10-29 |  |  |  |  |

Formula in C2, filled to C7: `=IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon","")`

Result: Renews soon; ; ; Renews soon; ; Renews soon

## How it works

Adding a number to a date moves it forward that many days, so `$F$1+$F$2` is the end of the window. AND keeps dates that are on or after the start and on or before the end. Delta, exactly 30 days out, is included; Cobalt has already passed, so it isn't flagged.

## Other ways

- `=IF(AND(B2-$F$1>=0,B2-$F$1<=$F$2),"Renews soon","")`: The same window written as "days until renewal", which is easy to adapt into a countdown.
- `=IF(B2<$F$1,"Expired",IF(B2<=$F$1+$F$2,"Renews soon",""))`: Labels past dates too, when expired contracts need attention. (Cobalt shows "Expired".)

## Common mistakes

- Testing only `B2<=$F$1+30` also flags every date in the past.
- Use `=TODAY()` in `F1` for a live flag, or the window moves only when you edit the date.

## Questions

**How do I show the number of days left?**

Subtract: `=B2-$F$1`, formatted as a number, not a date.

**How do I count contracts renewing in the window?**

Use COUNTIFS: `=COUNTIFS(B2:B7,">="&F1,B2:B7,"<="&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-due-in-next-30-days