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.
Automate RYGB based on Follow-up Date by Week
Community:
I have tried but cannot figure out how to make this formula work. Probably something very simple, and I am overthinking.
Any guidance would be much appreciated.
Thank you,
Sandy
Scenario: Regardless of the day of the week I open my sheet:
If [Follow-up date]1 is between M - F of the current week, RYGB symbol = "Green"
If [Follow-up date]1 is between M - F of the previous week, RYGB symbol = "Yellow"
If [Follow-up date]1 is older than M of the previous week, RYGB symbol = "Red"
Comments
-
Hi,
Here's a formula for you to try. This will go in the RYB column.
=IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()), "Green", IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()) - 1, "Yellow", IF(WEEKNUMBER([Follow-up date]1) < WEEKNUMBER(TODAY()) - 1, "Red", "")))
Not sure what you wanted for weeks in the future, so I made that blank. Feel free to change it.
Enjoy
Shawn
-
Shawn:
Your formula worked beautifully. Thank you, thank you, thank you... however, I did not provide the other criteria.
Below is my formula (including yours).
If you can show me what I am doing wrong, I would appreciate your help.
Sandy
What I am trying to say is:
If the date closed is not blank turn symbol blue
If the status is "resolved" turn symbol blue
If the status is blank and follow-up date is blank, insert "No Follow-up Date"
Otherwise, etc. [your formula picks up here]
=IF([Date Closed]1 <> 0, "Blue", IF(AND(Status1 = "Resolved"), "Blue", IF(AND(Status1 = ""), "", IF(AND([Follow-up Date]1 = ""), "Follow-up Date is Blank", IF(WEEKNUMBER([Follow-up Date]1) = WEEKNUMBER(TODAY()), "Green", IF(WEEKNUMBER([Follow-up Date]1) = WEEKNUMBER(TODAY()) - 1, "Yellow", IF(WEEKNUMBER([Follow-up Date]1) < WEEKNUMBER(TODAY()) - 1, "Red", "")))))))
-
Here you go...
=IF(OR([Date Closed]1 <> "", Status1 = "Resolved"), "Blue", IF(AND(Status1 = "", [Follow-up date]1 = ""), "No Follow-up Date", IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()), "Green", IF(WEEKNUMBER([Follow-up date]1) = WEEKNUMBER(TODAY()) - 1, "Yellow", IF(WEEKNUMBER([Follow-up date]1) < WEEKNUMBER(TODAY()) - 1, "Red", "")))))
-
Shawn:
I cannot thank you enough so thank you again. Works beautifully still, but of course, no good deed goes unpunished... my last hurdle is # INVALID DATA TYPE.
I assume this error is because both the follow-up date and the date closed fields are empty. Would I use ISBLANK and if so, how would I incorporate that?
Unfortunately, I am a novice (obviously) with formulas and do not even know how to begin trying to hide this message.
Sandy
-
No, if both are blank, "No Follow-up Date" will show. Check that the rows and columns are correct.
Shawn
-
Thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives