Conditional Formatting Based on Start and End Dates

[tps49]

Hello everyone,

I'm trying to structure a rule within conditional formatting that will be responsive to start and end dates of construction projects and to change the color of a cell that falls within those dates. To clarify, I have the following structure in my spreadsheet:

- A top row with dates by month (starting with Jan-13 and using an EOMONTH formula to project out afterwards).
- Two columns with a start and end date, respectively for the construction project (these are formatted as mm/dd/yyyy).
- Everything after those two columns is blank (so 36 blank columns representing Jan-13 through Dec-15 for each potential project date)

What I want the conditional formatting rule to do is look at the start and end dates of the project and color the cells if those months are within the project range; for instance, if a project lasts May-13 to Jan-14, every cell between and including those months would be colored.

I'm thinking that the best way to do this is to next index and match within an offset function here, but I cannot build a clean way to do it. Does anyone have a suggestion that might solve this?

Thanks in advance for any guidance you can provide, and don't hesitate to let me know if I can clarify something. Cheers.

 

[Fazza]

Try an AND function

something like below to suit your set up. I think your header dates are all end of month

=AND(start <= month's end, end > previous month's end)

If that isn't right, please adjust to suit

regards

 

[tps49]

Okay, makes sense. Anything else I need to watch out for? This seems too simple, but certainly possible that I overcomplicated this

 

[Fazza]

Just check (1) that it works correctly on some sample data, and (2) at the extreme earliest & latest dates. cheers

 

[tps49]

Just check (1) that it works correctly on some sample data, and (2) at the extreme earliest & latest dates. cheers

This worked beautifully. Thanks so much for your kindness and help.

 

[Fazza]

you're welcome