Conditional formatting IF

[Tre423]

Hello - I am new to the forum and am hoping someone could help.
I am trying to perform conditional formatting based on a formula with a few components. Although I am happy to break this up into seperate conditions if that is easier.

In short, I have a start date (B2), end date (c2), and a yes/no column (D2). I want the conditional formatting to look at a row of calendar date (E1, F1, G1...) and fill them with color based on whether the calendar dates fall between the start and end date. Furthermore, IF omplete (D2) = yes, I would want one color. IF complete (D2) = no I would want a different color. (the colors themselves dont matter - i just want to distinguish them.

I imagine this is relatively simple...I just cant seem to nail it. Any help is appreciated.

Capture.PNG

[Pete_UK]

Select the cells that you want this to apply to, from E2 across to however many columns you need and down however many rows that you have. Then click on Conditional Formatting | New Rule | Use a formula ... (from the bottom of the list), and then enter this formula in the dialogue box:

=AND(E$1>=$B2,E$1+1<=$C2,$D2="Yes")

Then click on the Format button | Fill tab and choose your colour for the Yes condition (grey). Then OK your way out. With those cells still selected, repeat for the other condition, using this formula:

=AND(E$1>=$B2,E$1+1<=$C2,$D2="No")

and this time choose blue for the fill colour and OK your way out. Excel will automatically adjust the cell references to suit the selected cells.

Hope this helps.

Pete

[Tre423]

Awesome! Thanks.
How about if I wanted to add one more condition, so I could add another color (red), that would indicate if the end date (c2) was in the past (<today) AND not yet complete (D2 = "no" ). Technically I have a cell that indicates todays date...but I am happy to use today() if easier. Capture2.PNG

[Pete_UK]

Rather than attach pictures of an Excel workbook, why not attach the workbook itself then I can put it in there for you and post back?

(Although you should be able to work it out from the above - you have two conditions in the AND function, like before).

Pete

[Tre423]

Sorry - like I said I'm new :-) I didnt see the option to add a file (only an image). Here you go
Example.xlsx
This is a sample of the data (its a simplified version of a much larger spreadsheet I am working on).

I realize this should be straight forward...I just have a mental block for some reason.

[Pete_UK]

Okay, well you will learn more if you actually do it yourself, so follow the instructions above and use this formula:

=AND(E$1<TODAY(),$D2="no")

and choose red for the background. Note that you only have two conditions within the AND function this time, separated by a comma, whereas for the other formulae there were 3. Note also where the $ symbols are in the formula (and the previous ones) - these prevent that part of the cell reference from changing when the formula is applied in other cells, so basically we want to compare the date which is always on row 1, and compare the contents of column D always.

Hope this helps.

Pete

[Tre423]

I totally agree - i'm trying to learn. And i get what you have here...my issue is that I think I need 4 conditions...the original 3 and then adding the condition if the end date less then today. That way it only highlights the cells between the two corresponding dates (start and end) with the red (to indicate its past due).