Return a value based on a date

Admin@PAT · 09/28/16

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 · 09/28/16

Your publish link did not work for me.

Admin@PAT · 09/28/16

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

Hope this works, looks the same to me?

Admin@PAT · 09/29/16

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 · 09/29/16

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

Admin@PAT · 09/30/16

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 · 09/30/16

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

Welcome to the Smartsheet Forum Archives

Return a value based on a date

Comments

Categories