Conditional Formatting no longer working
I enabled formatting that was disabled. I changed the # of days in the formatting but it's not working for all cells. Some of them are not changing.
It is working for project id's but not all of them.
Best Answer
-
Gotchya - insert the new column like you were starting, and choose "Text/Number" as the column type. Then paste the formula that I gave you. After pasting it in, rightclick on the cell where you put the formula and choose "convert to column formula" and that will make it a full column.
Answers
-
You are only applying the conditional format to the project ID column - apply it to all the columns you want colored (just click on three links where it says "project ID# column" and select all desired columns).
-
Sorry, I was not clear originally. I only need it change the Project ID column. It's not changing all of them to a color.
-
ahh - your criteria don't allow for a condition where something is 31 days old or doesn't have a date. You may want to modify your red criteria to be anything older than 29 days (rather than 30 days or younger) to catch something that is 35 days old and add something like a blue criteria for anything that simply doesn't have a date or other criteria that are forcing it to not fit into your date criteria.
-
I thought that the over 30 days would catch anything over 30 days old.
How would I do it then if I need it to be like below?
0-4 weeks Green
5-8 weeks yellow
over 8 weeks red
-
Unfortunately there isn't a "is not in the next X days" choice under the conditional formatting - so the simplest way may be a helper column populated with =today()-[Date Submitted]@row and then run your conditional formatting off of that helper column:
if helper is <0 then highlight project blue (this catches anything where the date submitted is in the future due to a typo or something)
if helper is <28 then highlight project green
if helper is <56 then highlight project yellow
if helper is >56 then highlight project red (this catches anything greater than 8 weeks old)
(edited to make it more clear- in your original list you had "is IN the last 30 days" which means <=30 days, not >30 days - this would make anything at 31 days default to not being red. What you wanted was "is NOT IN the last 30 days", but this is what Smartsheet doesn't offer and the helper column works around)
-
Where can I find how to do that? I am looking on the help and learning but it's just giving basic info.
-
What do you mean? How to add the helper column (just insert a column and enter the formula "=today()-[Date Submitted]@row" and then make it a column formula, then just modify your conditional formatting to look at your new helper column instead of "Date Submitted" with "is less than" and "is greater than" rather than "is in the next X days") or how to figure out when you've got to do weird helper column nonsense to work around a weak set of choices in SmartSheet (where the answer basically breaks down to "do a ton of really weird stuff until it starts feeling normal, then answer a bunch of questions in the community here to stress your definition of 'normal' even more").
Honestly, the most legitimate answer to your latest question is to do exactly what you did - when in doubt, post here (or troll through past answers via google to see if anyone did anything similar)! :)
Edited - you'll note, for example, that my second reply REALLY assumed that there WAS a "is NOT IN the next X days" choice. It wasn't until you came back with a reply and I started testing it that I realized that that, weirdly, isn't there… Your question forced me to figure out how I'd solve your problem in light of the obvious choice not being possible and that will help me tackle the next issue that I have that is similar.
-
BTW, if you get a lot of error messages, wrap your formula in your helper column with an iferror() statement like this:
=iferror(today()-[Date Submitted]@row,"")
that will give you blank cells instead of error messages and look a bit nicer.
-
I try google before I post here when I am needing help on something. Sometimes I find it and other times no.
I apologize, I am not well versed in Smartsheet. How do I add the formula? It's not giving me that option.I added a column to see if it became an option after but nothing there either.
-
Gotchya - insert the new column like you were starting, and choose "Text/Number" as the column type. Then paste the formula that I gave you. After pasting it in, rightclick on the cell where you put the formula and choose "convert to column formula" and that will make it a full column.
-
Thank you so much!!!! I would have never figured this out on my own. I appreciate the help and patience.
-
Glad I could help!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives