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
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.
Comments
-
Your publish link did not work for me.
-
https://app.smartsheet.com/b/publish?EQBCT=5b60441a73dc4bfe9c4e77977e0cce7c
Hope this works, looks the same to me?
-
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")
-
You need to add your next IF() statement after the "Overdue"
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives