Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
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
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))
