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

See it work

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

How it works

In Google Sheets

The same formula works in Google Sheets: =IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon",""). 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.

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.

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.

Related answers

• How to check if a cell is numeric in ExcelUse ISNUMBER inside IF: =IF(ISNUMBER(B2),"OK","Fix"). It is TRUE only for real numbers, not text that looks like one.
• How to check if a date is past due in ExcelFlag unpaid rows whose due date is before today: =IF(AND(C2="",B2<$G$1),"Overdue",""), with =TODAY() or an as-of date in G1.
• How to check if a number is between two values in ExcelUse AND with both limits: =IF(AND(value>=low,value<=high),"In range","Out of range").
• How to check if a value is within a tolerance in ExcelCompare the distance from the target with the tolerance: =IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail").
• How to check if neither of two conditions is true in ExcelWrap OR in NOT: =IF(NOT(OR(B2="Yes",C2>=$G$1)),"Reject","Review") is TRUE only when both conditions fail.
• How to use IF with AND and OR together in ExcelNest OR inside AND (or the other way round) to match the rule: =IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No").

Functions used: IF · AND

Lessons: The IF function · AND, OR & IFS · LET (Pro)