Search code examples
google-sheetscalendarrangeconditional-formatting

Conditional formatting based on another cell's value applied to date ranges

enter image description here

Hi there, I'm trying to create an assessment calendar like the one above. Teachers will add in a start and end date and the corresponding boxes will be highlighted. I'm trying to figure out the formula for this in conditional formatting as the correct date along the range of dates in the year need to be highlighted. Each teacher needs their own section for inputting their assessments and I'm not sure how to put in the formula properly. I will use a drop down menu for teachers to select dates... would anyone be able to help with this? I tried following this guide but I'm not sure if it applies: https://stackoverflow.com/questions/20545961/conditional-formatting-based-on-another-cells-value#:~:text=Add%20a%20comment-,20,So%20this%20answer%20is%20meant%20for%20completeness%20to%20the%20previous%20ones.,-It%20should%20demonstrate

I tried using the $ in front of my cells but the color doesn't seem to change. I tried =E34=45033 which changes that cells color, but it means I have to do a different formula for every cell. 45033 corresponds to the date 2023/04/17

Solution

  • Sample

    In this Sample, D2:T2, A3:B are all date format.

    conditional formatting

    Apply range of the conditional formatting is: D3:T

    The formula of the conditional formatting is as simple as:

    =AND(($D$2:$T$2>=$A3),($D$2:$T$2<=$B3))

    You can format te date value of D2:T2 into any format as you like, just make sure those cells contains a date value.