Conditional formatting cells

[GemMellow]

Hello

I am trying to set up a staff spreadsheet so I can track when the qualifications are due and I cant get my formulas right to change the cells. Our qualifications are renewable yearly so I would like the cells to change to yellow when they are due in 6 months orange when they are due in 3 months and red when they are overdue.
Could someone help with the formulas please?

 

[tyija1995]

Hey you can setup 3 CF rules, say you have your dates in cells H11:H13 then use these rules:
Select cell H11 (first cell with date - as its easier this way) -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
Rule1: =$H11-TODAY()<0 --> Apply a red fill and applies to range $H$11:$H$13
Rule2: =$H11-TODAY()<=90 --> Apply an orange fill and apply to the same range
Rule3: =$H11-TODAY()<=180 --> Apply a yellow fill and apply to the same range

Make sure the rules are ordered in this manner (red - orange - yellow) (under Manage Rules - ensure that you apply the change after you move it in the correct order)

 

[GemMellow]

I have added the formulas as above but it is just turning the whole sheet red even though some dates are within 6 months, not sure what I am doing wrong and it's very frustrating

 

[tyija1995]

It should work, when you say the whole sheet red does that mean EVERY cell in the sheet? If so then the "Applies to" has been set incorrectly it sounds like. It should apply to your range of dates that you want to colour. To adjust for the blank cells showing up as red within the CF range then the formula needs to be adapted to look at non-blanks only. This can be done by editing the existing rules formulas as such:
Rule 1: (RED) =AND(NOT(ISBLANK($H11)),$H11-TODAY()<0)
Rule 2: (ORANGE) =AND(NOT(ISBLANK($H11)),$H11-TODAY()<=90)
Rule 3: (YELLOW) =AND(NOT(ISBLANK($H11)),$H11-TODAY()<=180)

 

[GemMellow]

I don't know what I am doing wrong but I still cant do it, thought I was quite good on computer but this has got me very confused!! going to try and simplify it adn see if I can make it work.

I want the cells with dates in the last 6 months to stay white, cells with dates in the last 6-12 months to go yellow and cells over 12 months to turn red, could you explain how to do that.

 

[tyija1995]

Perhaps test with 1 specific cell to start with, so just put a date in a cell and use that as a basis for testing. It's working fine for me so I am not entirely sure why it's not for you, is there any way you can send the file across or send a picture of the rules you have setup?

In the last 6months etc I assume you mean from today's date, again that will be a similar process using CF to do so.

 

[tyija1995]

See attached file:

https://www.dropbox.com/s/ij7zju0dfvgr3lc/CF_Dates_Custom.xlsx?dl=0

From that you can download and take a look at the CF rules I have setup, altered them slightly from the post to be more accurate in reading 3 & 6 months.

 

[GemMellow]

Ok so this is the document which I have put the dates my staff did the training courses on and what I would like is the cells to change to yellow 6 months from the date in the cell, orange after 9 months and red after 12 months. Is this possible?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.dropbox.com/home?preview=Staff+matrix.xlsx[/FONT]

 

[tyija1995]

Hey,

The link you provided didn't work - remember you have to click the Link button first then enter the URL in when the prompt pops up.

When you have sent the file in that way I'll be more than happy to take a look for you.

 

[GemMellow]

Hi,
How do I post a link? I cant see a link option?