Conditional Formatting and Dates
1) I would like to have a conditional formatting rule for dates.
I keep track of documents that expire annually for each member of my team. I want to input into a cell the date that the document will expire. When the document will expire in 60 days, I want that cell to turn yellow. When it will expire in 30 days, I want that cell to turn orange. When that cell is expired, I want it to turn red.
2) I would also like to know how to set up my sheet so that if I add a new column (when a new person joins my team I will need to make a new column for them), it will copy the formatting done in the previous column automatically.
Best Answer
-
Make sure the column you are referencing in your conditional formatting rule is set as a date type column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You would click on the conditional formatting button to apply conditional formatting to a column based on a date being within a certain number of days. They will run from top to bottom and will stop on the first one that triggers as true. If you build them out and find they need to be in a different order, you can click and drag to rearrange them.
To copy a rule, you would select the dropdown arrow to the left of the rule and select the option to clone. You can then tweak which column it is referencing and go from there.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
1) You can totally do this in the Conditional formatting section located here. ( Under the Star next to the highlighter)
It will pop up a window that looks like the image below where you can make all those conditions super easy
2) For your second point, If you have any conditional formatting you want to roll over I dont think you can just make a copy of a column thats formatted. If t was a formula you wanted to copy over you would just have to copy paste it into the new column. Fortunately making conditions inst to labor intensive so hopefully that helps!
Ricky T
Gov Contractor for USSF
-
Thank you Ricky. But there is no option in here that says "is less than 60 days" or 30 days, etc. Where is the option that will make the cell change color based on the date? For example, in my cell I have entered that the document will expire on 10/11/2023. I want to do a conditional format that will change this cell red because it will expire in 30 days.
But there is no option below for that.
-
Make sure the column you are referencing in your conditional formatting rule is set as a date type column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That fixed it! Thank you.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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