Highlight cells based on the dates

[subodh.sapkota]

I want to highlight cells based on the dates in columns D & E.


Basically, based on the dates in column D & E (which are start and finish dates), the corresponding cells from Col G onwards should be highlighted reflecting the period of the dates. So, in row 7, as the first cell is Dec-22 and greater than start Date this has to be highlighted. For, completion date, the completion is Mar-26. The period after Mar-26 is Jun-26 So, the cells from Dec-22 to Jun-26 should be highlighted.
I tried using conditional formats, but doesn't seem to work.

If the date is say Mar-26 (for Row 7) , then cell N7 should also be highlighted as it refers to period after end of Dec to end of june.

Same thing for Row 12, the Dec-27 cell should be highlighted when sep-27 is the completion date in column E (cell E11).

Any help will be appreciated.

Thanks in advance.

[FDibbins]

1. your ranges seem disjointed (that shouldnt affect the results, it's just simpler to use complete ranges so you dont miss anything...in fact J9 may be missing)

2. Your CD formula is a bit involved. What exactly are you testing for? (it looks like part of that formula is testing an empty cell L2 against another empty cell (K26), a text cell (K27) and a cell with an error in it (K28)

[subodh.sapkota]

Hi FDibbins,

Thanks for your suggestion.

Basically, based on the dates in column D & E (which are start and finish dates), the corresponding cells from Col G onwards should be highlighted reflecting the period of the dates. So, in row 7, as the first cell is Dec-22 and greater than start Date this has to be highlighted. For, completion date, the completion is Mar-26. The period after Mar-26 is Jun-26 So, the cells from Dec-22 to Jun-26 should be highlighted.

[kvsrinivasamurthy]

Select G7:Q12. Formula for CF.

Please Login or Register  to view this content.

[Pepe Le Mokko]

Are you still using XL2013?

[subodh.sapkota]

Thanks for the pickup, adjusted the version.

[subodh.sapkota]

Thanks kvsrinivasamurthy for the input.

However, this isn't working as expected.

For example, For row 7, Jun-26 cell should also be selected as Mar-26 falls on this period. Similarly, for row 9, Dec-25 (ie cell M9 should also be highlighted.)

In row 12, Jun-25 hould not be highlighted only starting from Dec-25 and one more cell uptp Dec-27 ie cell Q12 should be highlighted.Capture.PNG

see attached screen shot for reference.

[DJunqueira]

You can use the formula bellow.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Revised formula for CF.

Please Login or Register  to view this content.