How do I set conditional formatting, based on dates, to span across columns representing quarters?

PGilbert

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!

Carson Penticuff

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)

PGilbert

Thank you so much! I am trying this now.