Highlight cells according to date range in other cells

[ejsdon]

Hi.
I have a simple sheet that I would like to format according to dates that are inputted in some columns.

If you see the attached file:

I have currently just manually entered the '1' figure corresponding to the dates dictated in columns D and E. I would like to have had these populated automatically.

When I have put in a New Finish date, I would like the appropriate cells to have be formatted differently (probably a coloured fill). So, any cell from that date up until the original Finish date to be highlighted.

The collection time would have to be formatted in a further colour, with those dates going out further than the New Finish date. For example, the Fridge Freezer New Finish Date is on 20-Dec-20 - I would like the next two weeks after that to be highlighted in a different colour than has been used for the other cells.

I hope this makes sense.

I would be able to do this myself if all the cells had the dates in them, but I need to be referring to the cells in row 2 - that's where I am having the problem!

Thank You in advance for nay help.

[Glenn Kennedy]

If you don't mind me saying so.... that's a pretty awful sample, as we have to scroll over 400+columns to see the data!!!

CF1 =AND(I$2>=MIN($E3,$F3),I$2<=MAX($E3,$F3))

CF2 =AND(I$2>=$D3,I$2<=$E3)

Apply to your entire range. I changed the dates in the first two rows to stop 00's of columns worth of scrolling.

[ejsdon]

Hi Glenn, Thank You.
Yes - I should've reduced the columns!
I'm unsure how to apply those formulae to the cells in my sheet.

[ejsdon]

I have attached now a better version, and manually entered how I would like the sheet to look like.

[Glenn Kennedy]

I'm away for a couple of hours. I just realised that my previous effort was no good. You need two colours for amended end dates... one for earlier than anticipated and another for later than anticipated.

[popipipo]

With 3 CF formula's I come to this result.

[popipipo]

Thanks for the reputation points

[Glenn Kennedy]

There are 3 conditions that need to be met.

1. Finishes on time (blue).
2. Finishes later than anticipated (extra days in red)
3. Finishes earlier than anticipated (days saved in green).

1. =AND(H$2>$D3,H$2<=$E3)
2. =AND($F3>=H$2,$E3<H$2)
3. =AND($E3<>$F3,$F3<=H$2,$E3>=H$2)

CF applied to: $H$3:$BM$8

[Shareez Saleem]

Please have a look at this.

I have use the below formula (Cell H12 and copied & pasted to the rest of the cells)

=IF(AND(H$11>=$D12,H$11<=$F12),1,IF(AND($F12+$G12<H$11,H$11<=$E12),3,IF(AND($F12+$G12>=H$11,H$11<$E12,H$11>C$14),2,"")))

Blue= New Start and End Date
Dark Blue= Between New Finish and Finish Date
Grey for other