Return a value based on a date

Admin@PAT

Hi there everyone,

I have posted before but am still stuck.

 

https://app.smartsheet.com/b/publish?EQBCT=5b60441a73dc4bfe9c4e77977e0cce7c

 

Hope this is helpful and clear.

Benjamin Walker

Your publish link did not work for me.

Admin@PAT

https://app.smartsheet.com/b/publish?EQBCT=5b60441a73dc4bfe9c4e77977e0cce7c

 

Hope this works, looks the same to me? 

Admin@PAT

Is there an IF/AND formula I can create???

 

I get this far, then my nesting simply doesn't work: =IF([Date Expected]11 < $[Date Expected]$1, "Overdue")

 

Benjamin Walker

You need to add your next IF() statement after the "Overdue" 

Admin@PAT

Thanks Ben, 

 

Yes, I have done that - and get  an error in my formula - my next 'statement' requires to look between 2 dates. 

=IF([Date Expected]11 < $[Date Expected]$1, "Overdue"

 Statement would be: If Date Expected11 is 'between' Date Expected 1 and Date Expected 2, "HOT" (and two more of these statements to look at date ranges and return Hot, Medium or Cold.

 

I cannot work out the 'between' logic in the formula. Does smartsheet support AND logic? 

 

=IF([Date Expected]11 < $[Date Expected]$1, "Overdue"), IF(AND([Date Expected]11 > $[Date Expected]$1, < $[Date Expected]$2 "HOT")

 

This formula give UNPARSEABLE result... I just can't work it out... hmmmph! I am missing a 'link' between the individual statements. The sheets shows what I am trying to achieve.

 

 

Benjamin Walker

Looks like you had a couple parenthesis missing and needed to supply the second operator for the AND! I fixed it on your sheet. 

 

Here is the formula I came up with:

 

=IF([Date Expected]11 < $[Date Expected]$1, "Overdue", IF(AND([Date Expected]11 > $[Date Expected]$1, [Date Expected]11 < $[Date Expected]$2), "HOT", "COLD"))

 

 

Hopefully with that you can continue building your formula. This link may help you:

https://help.smartsheet.com/articles/775363-using-formulas#logic