Need help with IF statement calculating Dates

[lorne17]

Hello there,

I have a cell that marks an action item as priority 1, 2, or 3 depending in criticality of the item. I'd like to change this from a manual entry and have it look at Column J (which is the due date column) and change the priroity of the number depending on the due date. If it's 0-7 days from due date I want it to be "1", if it's 8-14 days from due date I want it to be "2", and if it's 15+ days I want it to be priority "3". I'd also like the priority to change to "PAST DUE" if the due date has passed.

How might I go about doing this? I started with a simple formula (not trying to tackle the entire formula quite yet) with the formula below. But unfortunately I keep getting an error message:

=IF((J7-TODAY()>=0,J7-TODAY()<=7),"1","2")

Thoughts?

Thanks,
Lorne

 

[Jonmo1]

Try

=MATCH(J7-TODAY(),{0,8,15})

 

[JoeMo]

Try:

<today(),"past due",if(and(j7="">=IF(J7="","",IF(J7 < TODAY(),"PAST DUE",IF(AND(J7>=TODAY(),J7<=TODAY()+7),1,IF(AND(J7>=TODAY()+8,J7<=TODAY()+14),2,3))))

</today(),"past>

 

[lorne17]

JoeMo, I get an error when trying yours

Jonmo1, your equation worked great. Now I'm trying to complicate things and can't get it to work. Basically if the Due By date in J8 is past due, I want it to say "PAST DUE". If the J column is blank, I want it to read "NO DUE DATE".
<today(),"past due",if(j8="" ,"no="" due="" date",match(j8-today(),{0,8,15})))[="" code]
<today(),if(j8="","no due="" date",match(j8-today(),{0,8,15}))[="" code]
<today(),if(j8="","no due="" date",match(j8-today(),{0,8,15}))
=IF(J8 < TODAY(),IF(J8="","NO DUE DATE",MATCH(J8-TODAY(),{0,8,15}))
Why won't this formula work? I keep getting an error. I did get this one to work:

=IF(J8="","NO DUE DATE",MATCH(J8-TODAY(),{0,8,15}))

Thank you!
Lorne</today(),if(j8="","no></today(),if(j8="","no></today(),"past>

 

[JoeMo]

JoeMo, I get an error when trying yours <today(),"past due",if(j8="" ,"no="" due="" date",match(j8-today(),{0,8,15})))[="" code]

Thank you!
Lorne

You need to remove the spaces around the first "less than" symbol ( < ) - had to put them there to keep the symbol from being interpreted as an html character.


</today(),"past>

 

[Jonmo1]

I assume then the match function errors when it's past due.

Try

=If(J7="","No Due Date",IFERROR(MATCH(J7-TODAY(),{0,8,15}),"Past Due"))

 

[lorne17]

I assume then the match function errors when it's past due.

Try

=If(J7="","No Due Date",IFERROR(MATCH(J7-TODAY(),{0,8,15}),"Past Due"))

I'd imagine that should work. But when I try it I get an error popup "This value doesn't match the data validation restrictions defined for this cell". When I click Retry it automatically changes the formula to this:

=IF(RC[7]="","No Due Date",IFERROR(MATCH(RC[7]-TODAY(),{0,8,15}),"Past Due"))

 

[Jonmo1]

What's the data validation rule for that cell ?

Come to think of it, if you're putting a formula in that cell, then you shouldn't have data validation in that cell also.
Remove the validation on the Data Tab for that cell

 

[lorne17]

Ok to follow this up, when trying this on a different spreadsheet it works? Not sure what's going on, any ideas?

 

[lorne17]

What's the data validation rule for that cell ?

Come to think of it, if you're putting a formula in that cell, then you shouldn't have data validation in that cell also.
Remove the validation on the Data Tab for that cell

Ok that worked. I needed to go to the Data Validation popup and select "CLEAR ALL".

Thank you!

Lorne