How do I set conditional formatting, based on dates, to span across columns representing quarters?
Hello! I am trying to create conditional formatting based on a range of project dates that will fill cells representing quarters. For example: if the project spans 5/6/24 - 9/1/24, I want it to shade the Q2 24 and Q3 24 cells. If the project spans 5/6/24 - 1/5/26, I want it to shade cells Q2 24 through Q1 26. See the dark purple bar in the example below. Thank you for your help!
Answers
-
I believe the best approach would be to setup a helper column for each quarter, then use the helper columns as conditions for the formatting. Here is a scaled down version of your sheet:
Here is the formula from the helper column pictured. You would need to add an additional column for each quarter and adjust the formula for each accordingly. You can then hide the helper column so they aren't an eyesore.
=IF(OR(AND([Start Date]@row >= DATE(2024, 01, 01), [Start Date]@row < DATE(2024, 04, 01)), AND([End Date]@row >= DATE(2024, 01, 01), [End Date]@row < DATE(2024, 04, 01)), AND([Start Date]@row < DATE(2024, 01, 01), [End Date]@row >= DATE(2024, 04, 01))), 1, 0)
-
Thank you so much! I am trying this now.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives