• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF or AND conditional formatting help

L

Lynsey

New Member
Hi,

I'm trying to get a formula for the following:

I need to have an automatic calculation for a report close date depending on the date the report was raised AND it's risk rating. The Close date cell also needs to change color depending on date.

So:

If the Risk Rating is Extreme then the officers have 5 working days to close the report based on the date the report was raised and the cell color would need to turn red.

I tried the following formula: =AND(risk="Extreme", AND(CLOSE DATE=DATE RAISED +5)) format RED

This didn't work as my brain just can't work out formulas.

Can anyone help with this problem?
 
Hi Lynsey ,

The syntax needs to be :

=AND(risk = "Extreme" , CloseDate >= DateRaised + 5)

then format the CloseDate cell RED.

Narayan
 
Hi, Thanks for the syntax.

It changes the color of the cell but I need it to show the date depending on what the risk level is and how many working days they have to close it from the initial raised date.

Extreme/High Risk = 5 working days to close
Medium Risk = 14 working days to close
Low Risk = 42 working days to close

Can it do this? I'm adding it to conditional formatting.

Thanks for your help :)
 
Hi Lynsey ,

Even better would be to use :

=AND(Risk="Extreme",OR(TODAY()>=DateRaised+5,CloseDate>DateRaised+5))

This would ensure that if the CloseDate were not entered , if it were 5 days after the DateRaised , the cell would still turn RED.

Narayan
 
Hi ,

Would the entered text be exactly the way you have mentioned it ?

Extreme / High Risk
Medium Risk
Low Risk

or would it have just :

Extreme / High
Medium
Low

or would it have :

Extreme
High
Medium
Low

Narayan
 
tried the last syntax you sent and it just changes the color of the cell.

I need it to show the date.

Thanks :)
 
Hi ,

There are two separate requirements that you are talking about :

1. Change of colour
2. Display of required close date

These are two different requirements ; the first one is to be done using CF , while the second needs to be a formula which is to be entered in the cell where you want the date to be displayed ; if this is the same cell where the user will manually enter the date , then when the manual entry is made , the formula will be overwritten , and will no longer be available ; however , the CF will remain , and can still turn the cell RED if the entered CloseDate is beyond the specified values. Is this OK with you ?

Narayan
 
I thought it might be two different formulas.

The changing the color in CF seems straight forward.

But in regards to the Close date - I want this to be an automatic entry rather than the user manually adding it. So the Closing date will come from the Date raised and either + 5, 14, 42 depending in what the risk rating is.
 
Hi ,

OK. In that case , the formula would be :

=IFERROR(IF(OR(Risk="Extreme",Risk="High"),5,IF(Risk="Medium",14,IF(Risk="Low",42,"")))+DateRaised,"")

Narayan
 
hi, just tried the new formula and it still doesn't put the date.

Also I need to split the extreme/High, medium and low to show different colors so I'm think I will need 3 separate formulas so I can have a red cell High/extreme, green cell for medium and no fill for low.

Sorry to be a pain, but thanks for your help :)
 
Hi ,

What does the formula display ?

Have you defined the named ranges Risk , DateRaised ?

Can you upload a sample workbook , so that I can put in everything and reupload it ?

Narayan
 
It's nearly there I think, but the problem now is we have the due date coming up but no colour and if I enter just the risk the color shows up in the due date but no dates. I need to two combined if possible.
 

Attachments

Hi ,

The problem is with your specification ; let us go back to an earlier post where I mentioned the two situations. Do you want the Due Date cell to be coloured , and filled with a due date through a formula ?

This is not very meaningful ; since the cell contains a formula , depending on whether the Risk cell has "Extreme" , "High" , "Medium" or "Low" in it , and depending on the Date Raised date , the Due Date cell will get some date , either 5 days or 14 days or 42 days from the Date Raised date.

Since the same formula is being used in the CF , the cells will also be coloured RED or GREEN ; is that what you want ? If so , we can simplify the CF to just look at the Risk cells ; if the Risk cell has "Extreme" or "High" in it , colour the Due Date cell RED , if the Risk cell has "Medium" in it , colour the Due Date cell GREEN.

Narayan
 
I think you've cracked it! What you have sent me works is just what I need.

Thank you for all your help! You are a star!
 
Hi, I'm having a few problems with this formula that was worked out for me above. I thought it was working ok until the other day when I noticed that some of the cells were not changing to the right color.

I've had to spilt extreme and high colors so now its as follows:

Extreme = Red
High = Orange
Medium = Yellow
Low = No color

I'm finding that once I apply the conditional formatting that some of the cells are not changing to the right color. Extreme is turning orange rather than red, medium being orange instead of yellow.

I've attached a sample workbook to explain what is going on.

Can anyone help??? I'm using Office 13
 
Hi ,

See your file. The problem was one CF formula referred to A3 and B3 ; remember that when you have a range specified in the Applies To box , the formula should always refer to the top-most cell in that range , unless you intentionally want to refer to some other cell. For instance , in your case , the CF formula should apply to the range C2 downwards ; I have put it as C2:C1000.

In this case , the CF formula should refer to A2 and B2 , not A3 and B3.

Narayan
 

Attachments

Hi,

I've double and triple checked what I have inputted into the CF and it's matching what you have but it still doesn't work.

I've attached what I am working on so you can see if there is anything I have done that's different to what you have put.

I just don't understand why it's not working.

Any help would be brilliant!
 

Attachments

You must log in or register to reply here.
Back
Top