Conditional formatting between values? Do I need to restructure my spreadsheet?

0
votes

I would like to format all cells within the range (essentially fill them with color) of start date to end date in each row.

For example, in the picture below, you can see that cells B8 and C8 contain start and end dates, 1/1/2013 and 3/24/2013 respectively. It is formatting correctly, coloring in cells D8:08.

Conditional Formatting Example

The header cells contain dates formatted as text. For example, cell D3 (the first J), is actually the date 1 January 2013. Similarly, cell E3 (the second J), is actually the date 8 January 2013, cell F3 is 15 January 2013, and cell G3 is January 22 2013. The pattern repeats for each month, only listing the 1st, 8th, 15th, and 22nd of each month.

PROBLEM: I believe I've got it working most of the way, but it seems to not format correctly if the dates fall outside of the dates assigned in the header cells.

For example, if I assign a start and finish date of 5/31/2013, no cells are highlighted. See below image.

enter image description here

I assume that's because I only have up to May 22 2013 in my header cells, but I would like to keep it to four weeks in each month.

Below is the formula I used to format, which I then applied to the whole range D8:AY43, which is essentially the whole task hierarchy.

=(D$3>=$B8)*(D$3<=$C8)

Is there anyway to change this formula to accommodate my issue (cells won't highlight if they fall outside the range of values designated in the month column headers?) Or do I have to restructure my spreadsheet?

Thanks!

1
excelexcel-formula
"I assume that's because I only have up to May 22 2013 in my header cells, but I would like to keep it to four weeks in each month." Why? There are more than 4 weeks in many months. All months, except February, actually. – David Zemens

1 Answers

1
votes

An alternative while keeping "4 weeks" per month would be to change the dates in header column to reflect the end of a "week", rather than the start. So instead of January 1, January 8, January 15, etc., use January 5, January 12, January 19, & January 31. You'll need to tweak your formula to account for that change, but it would preserve the structure of your workbook.

Not 100% certain what you're doing (looks like a Gantt chart?) but looks like you're just trying to evaluate whether the date in Column D is between the start and end dates in cols B & C, in which case:

=AND($B8<=D$3,$C8>=D$3)*1

This tests whether the date in D3 falls between the start/end dates defined in columns B & C, and returns "1" if true, and "0" if false.

To put this in conditional formatting, do the rule like:

=AND($B8<=D$3,$C8>=D$3)*1=1

Here is what the rule should look like in the Conditional formatting dialog:

conditional formatting rule