#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Return a value based on a date

Options
edited 12/09/19

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.

• Employee
Options

• Options
• Options

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")

• Employee
edited 09/29/16
Options

• Options

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.

• Employee
Options

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"))