Conditional formatting on dates

[aajwenner]

Good Afternoon All,

I am currently working in a humanitarian organisation that has many projects running on tight deadlines. I have each of my activities in a different row, with start and end dates in the next columns (G6:G146 and H6:H146 respectively). I would like to apply conditional formatting to the end dates so that if if the end date is further than 5 days away from now, it is green, if it is within 5 days of today, yellow, and if it has passed today's date (i.e. it has passed the deadline), it turns red.

To complicate matters further, I would really like for it to turn green if the "Progress" column (Column I) says "Done". This is so that if there is a project that has been marked as done, the finish date turns green, so that it is easy to see what there is left to do especially over the deadline. But if it is too much of a hassle, let's walk before we run eh!

Any help is much appreciated, as the visual colour scheme will help keep everyone on track (hopefully!).
Thank You
Nye

 

[Caleeco]

Hello aajwenner,
Select all of your data, Open the Conditional Formatting Menu, Select 'Use formula to determine which cells to format'

Then set up these rules... (remove all spaces around the " > " and " < " symbols
<today())[ code]

Green Cells

=OR(I2="Done",H2 > TODAY()+5)

Yellow Cells

=AND(I2 < > "Done",AND(H2 < TODAY()+5,H2 > TODAY()))

Red Cells

=AND(I2 < > "Done",H2 < TODAY())

Note: None of the cell references should be fixed i.e $H$6.

Let me know how you get on with it
Thanks
Caleeco</today())[>

 

[aajwenner]

Hi Caleeco,

Thank You for the response. I have tried following your instructions but it doesn't seem to have worked. I noticed you had I2 and H2, but my data doesn't start until I6, H6, so I changed those digits, but all of the end date column has turned red. I'm not really sure why it has done this though.
Hopefully this image has uploaded...let's give it a go.

 

[aajwenner]

Hi Caleeco,

Evidently, this screenshot has not worked, but what it was showing was that they are all red, and that the formula in the box (for red) says:

=AND(I1048435<>"Done",H1048435< TODAY())

I have no idea why it says I1048435...is this right?

Thanks very much for your help!
Nye

 

[Caleeco]

Hi Caleeco,

Evidently, this screenshot has not worked, but what it was showing was that they are all red, and that the formula in the box (for red) says:

=AND(I1048435<>"Done",H1048435< TODAY())

I have no idea why it says I1048435...is this right?

Thanks very much for your help!
Nye

Hello aajwenner,

Those formulas do not look correct. Please delete ALL CF from the data we're Working with:
1. Select Cells
2. On the Home ribbon >conditional formatting > Clear Rules > Clear conditional formatting from selected cells.

Then follow these steps.
1. Select Cells H6:H146
2. Go to Home Ribbon > Conditional Formatting > New Rule > use a formula to determine which cells to format
3. Type MANUALLY (dont copy/paste) the first formula below
4. set the formatting eg Cell Fill colour
5. repear steps 1-4, for the other 2 formulae

Apologies, you were correct, I was using range H2, when the formulas should have been starting at H6

Let me know how you get on
Thanks
Caleeco

 

[aajwenner]

Hey Caleeco,

Yes after making those changes, it all seems to have worked! (I edited the H62, I assume you meant H6).

Thanks very much for taking the time to answer, and then to follow up!
Much appreciated
Nye

 

[Caleeco]

Hey Caleeco,

Yes after making those changes, it all seems to have worked! (I edited the H62, I assume you meant H6).

Thanks very much for taking the time to answer, and then to follow up!
Much appreciated
Nye

That's great :D Glad you got it working!

That's no problem, I'm always happy to help

Regards
Caleeco