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.
Conditional Formatting & Date Period
Hi Team,
I am wanting to apply CF to a sheet with a date field.
I would like to apply CF when a date falls (from todays date) within 3 months, another CF for 3-6months and another with 6+ months - thing is this becomes a 'rolling' CF, aeaning that at some point the CF will change or adapt based on todays date. I am not sure if this makes any sense...
Context: Leads List with column titled: Date Expected
Based on this date (Date Expected) the CF will apply.
So...
If 'Date Expected' is within 90days (from todays date) CF 'yellow' apply to row
If Date Expected is greater than 90days but less than 180 days (from todays date) CF 'Orange' and so on for great than 180days
Is this at all possible, I am happy with a workaround, also happy with a workaround to a formula that would return a value
If Date Expected is today + 60 return HOT, if +180 return MEDIUM etc...
Sorry this is jumbled, I know what I need to achieve, just not how to get there as the CF options for Date columns seem limited in this instance.
Comments
-
Your first condition can be achieved via Conditional Formatting by using "is in the next (days)", because that condition uses days vs. a fixed date. However, the greater than 90 but less than 180 AND the greater than 180 days arguments are not achievable via CF conditions. Since the arguments needed to get those results force you to select an actual date.
What you can do is create a column for storing a value 1=within 90 days, 2=>90 and <=180, 3=>180. Use the IF formula against "date expected" to get the value.
Now you can set your CF based on the value of the column being 1, 2 or 3 and you can hide the column that holds the numeric value.
Hope that helps.
-
Hi Darren
Yes that is exactly it. Thanks, I just am not sure about the logic of the formula to return the 1, 2, or 3 result.
Thanks - I will keep trying
-
Hi Everyone,
Can anyone please advise the logi formula to return a 1, 2 or 3 value.
if <date expected> (is less than 90days from today, return value '1'.
I am stumped!!!
-
I hope you solved this already, but in case you did not ...
=IF([Date Expected]23 - TODAY() > 179, 3, IF([Date Expected]23 - TODAY() > 89, 2, IF([Date Expected]23 - TODAY() >= 0, 1, 0)))
will give the results
0 - in the past
1 - today through 89 days (or blank)
2 - 90 - 179 days
3 - 180 days and beyond.
Hope this didn't help (September seems a long time ago)
Craig
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