Formula Problem

[Miranda868]

I am trying to turn entire rows red/neutral/green based on the date range in the F and H columns. I have tried these formulas and i'm not having any luck:

Red Formula: =AND($F6<TODAY(),$H6<TODAY(),$I6=0)

Neutral Formula: =AND($F6>TODAY(),$H6<TODAY(),$I6=0)

Green Formula: =AND($F6>TODAY(),$H6>TODAY(),$I6=0)

I also tried these, and they just cause everything to be highlighted:

Red Formula: =IF(AND($F6<TODAY(),$H6<TODAY()),$H6<>"Processing")

Neutral Formula: =IF(AND($F6>TODAY(),$H6<TODAY()),$H6<>"Processing")

Green Formula: =IF(AND($F6>TODAY(),$H6>TODAY()),$H6<>"Processing")

Can someone assist me with this? I'm not sure where I'm going wrong.

Thanks in advance!

 

[SirJB7]

Hi, Miranda868!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, give a look at the uploaded file. I found that you haven't set rules for the 3 conditions described above, and that there were a lot of fractionated rules by subsets of rows. This is usual when you copy, move or delete rows. BTW check that the 3 conditions don't consider the = in the comparison for < and >, and I think they should.

Regards!

 

[Miranda868]

Thank you!!

I checked the link, I appreciate it. I only have one more thing that's confusing me. I was able to get my processing words to turn green, but now I'd like to highlight the 7/14/21/28/25 Days Processing dates in colors as they are reached. The formula I came up with is this:

=IF($H6="Processing",$J6<=TODAY(),$J6>$I6+5)

The problem is that it seems to be ignoring the "If $H6="Processing" part, because it's turning dates orange that arent processing. Can you tell me what I'm doing wrong? I really appreciate the help!

 

[SirJB7]

Hi, Miranda868!
Have you manually tested which cells of column J should fit into this CF? If not, please do it. Could you post the list, here or at a new worksheet, row numbers only? Thanks.
Regards!

 

[Miranda868]

How do I manually test? Sorry I'm pretty new to formulas

 

[NARAYANK991]

Hi ,

I have a suggestion , which is a general one.

Please post your requirements in a clear , comprehensive fashion , and let those who wish to answer decide on the formulae which need to be used.

Please do not expect members to understand your logic from your posted formulae , which may or may not be adequate.

Just list down what you want done , the conditions under which this should happen , and leave the rest to the members of a forum.

Narayan

 

[Miranda868]

Sorry, I thought I did that in my first sentence. The formulas were meant to be additional. Like I said, I am really new to this. I appreciate your feedback, particularly that you're nice about it. I received a rather rude message about my post already.

 

[NARAYANK991]

Sorry, I thought I did that in my first sentence. The formulas were meant to be additional. Like I said, I am really new to this. I appreciate your feedback, particularly that you're nice about it. I received a rather rude message about my post already.

Hi ,

I am sorry about that ; I went through your original post , and was confused because you have posted two sets of formulae ; which one is supposed to be used ? What I mean is , in one set of formulae , the contents of column H having the text string Processing are not used , while in the other set of formulae , it is used.

What is the logic that is required by you ?

Do you want only dates to be considered or should the text string in column H also be considered ?

Narayan

 

[Miranda868]

I see, okay so:

-If column H says "Processing" then there is a date in Column I.

-Only if there is a date in column I do I want Column J to turn Orange.

-And Column J should only turn orange if it is a week or more from the date in Column I.

I will eventually format columns K-N, but if I can figure out how to correctly format column J I know I'll be able to figure out the other 4 based on that formula.

 

[Miranda868]

Thanks again for the help, I was reading fast and scrambling at work, I should have been specific with my title among other things.

I've been in a complete tizzy trying to organize Mt. File and this spreadsheet will be my saving grace!

 

[NARAYANK991]

Hi ,

Let me summarize what I have read.

1. You wish to conditionally format only column J ; is this correct ?

If the rule for conditionally formatting columns K through N is the same as for column J , all of them can use the same logic ; you do not need to have multiple rules for multiple columns unless the logic is different for each column.

2. Column J is to be formatted orange if it contains a date which is a week or more than the date in column I.

If column I does not contain a date , nothing needs to be done ; is this correct ?

Is this all ?

Narayan

 

[Miranda868]

Well, K-N will only get highlighted if they are 2, 3, 4, and 5 weeks past the date in column I. So it's different for each one, that's why I figured I'd request help on J and then apply what I learn to the rest.

1. Correct
2. Correct

And yes, if column I does not contain a date, nothing needs to be done.

 

[NARAYANK991]

Hi ,

See the attached file.

Your data in columns J , K , L , M and N is through a formula which ensures that your conditions will never be satisfied.

I have manually changed certain cells so that you can see the effect in all the columns.

The CF rules have been inserted for all the 5 columns ; nothing more needs to be done.

Narayan

 

[Miranda868]

Ohhhh okay, thank you so much, I appreciate the help! I'm going to read some of the site resources once I get caught up with my files so I can improve my general skills. Hopefully the next time I post I'll be able to more clearly articulate my issues from the start.