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.
Status field based on next week
Hello,
I have a status field that I would like populated with "Upcoming" if the Start Date is next week. I can't seem to find a way to do this because if I use something like =IF([Start Date]8 > TODAY() + 7, "Upcoming") but that only works if on Mondays.
Thanks in advance!
Lisa
Comments
-
Your inequality sign is backwards. Take a look at that task that "upcoming" task on a Monday. I'd put good money on that task being over a week away from starting. The Gantt chart will be a nice way to check yourself here...maybe add conditional formatting for "Upcoming" to change the Gantt bar color or something like that.
Recognize, too, that everytime you open up the the sheet on a new day, you might have to save it (because the calulations using the today() function could have a new result! I tested your formula out on my own and it does indeed work for all days of the week.
You might also check that you're referering to the correct row (I assume you copied your formula out of row 8)
We do a similar thing, but instead of "upcoming" we simply return the number of "weeks out" a task is. We like this because you can start to do some powerful things because this column's information is a number. A whole world of logic then opens up.
For example, we can then sort tasks by start date in reports, show this "weeks to start" field, and get a quick vision of the grouping of tasks, and see which weeks are looking heavy, and which are looking light. That formula looks like this (any tasks which is complete gets a "-"):
=IF([% Complete]1 = 1, "-", IF([Start Date]1 < TODAY(), 0, INT(([Start Date]1 - TODAY(1)) / 7) + 1))
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives