CODEWITHSUNDEEP
excelformula
JESS
JESS

Reputation: 1

Excel Formula Help: Within 1 week, but no more than one week

I am looking to create a formula in which

  1. if the date is passed today it will display "OVERDUE",

  2. if the date is within 7 days from today/a week from today then it would display "1 week left",

  3. but if the date is more than a week away then the cell will remain blank.

The Overdue and 1 week left command are working, but not the third. I don't know how to contain the 1 week formula to just within 1 week so that it doesn't overlap with the third.

e28 and down is my date assigned column g28 and down is my reminder/status column

=IF(ISBLANK(E28),"", IF(E28TODAY()-7,"1 WEEK LEFT!", IF(E28>TODAY()-8,""))))

*sorry I'm new. This post is erasing my less than and greater than signs.

Your help will be greatly appreciated!! Thank you in advance.

Upvotes: 0

Views: 1286

Answers (2)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

Try the following formula:

=IF(ISBLANK(E28),"",IF(E28<TODAY(),"OVERDUE",IF(E28-TODAY()<=7,"1 WEEK LEFT!",IF(E28-TODAY()>7,""))))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Use

=IF(E28-TODAY()>0,"Overdue",IF(TODAY()-E28<=7,"1 WEEK LEFT",""))

Upvotes: 1

Related Questions