Formula help return value based on criteria and date

[Ekguy41]

Hello,

I am trying to create the formula below and hitting a brick wall again. Any help would be greatly appreciated.

if A1 = Green and A2 = - then good
if A1 = None and A2 <> then bad
if A1 = Amber or red and A2 <> - and A2 is less than today then bad else good

 

[Joe4]

Are you looking for the words "Green", "None", and "Amber" in cell A1, or are you looking for the color of the cells?
Note that native Excel formulas can only run off of the values in the cells, not on the formatting of cells. You would need VBA to do something like that.

If the colors in A1 are set by Conditional Formatting, you may be able to use the same criteria that Conditional Formatting is using in your formulas to do what you want.
If that is the case, please let us know what your Conditional Formatting rules are.

 

[Ekguy41]

Hi, I am looking for the text green, amber, red, none.

 

[Joe4]

What exactly is in cells A2?
From your description, it sounds like either "-" or a date. Is it really the character "-", or is that just special formatting for a zero/blank entry?

Also, in your second condition, what is the condition for A2? You seem to have left something off (not equal to what?):

if A1 = None and A2 <> then bad

 

[Ekguy41]

Hi, A2 will either be - character or date value

 

[Joe4]

Hi, A2 will either be - character or date value

OK, you didn't really answer my question about your second condition (you have A2 <> and then nothing after that, so I am not sure what it is supposed to be not equal to).
And it is important to note that an actual entry of "-" is very different thank a blank or zero that is formatted as "-".

So I have to make a few assumptions which may or may not be accurate. But you will need a nested IF statement, something like this:

=IF(AND(A1="Green",A2="-"),"Good",IF(AND(A1="None",A2<>"-"),"Bad",IF(AND(A1="Amber",A2>0,A2<TODAY()),"Bad","Good")))

I color coded each part of the nested IF so you can more easily see what each part is doing.

 

[Ekguy41]

Apologies A1 =“None” and A2 <> “-“ then bad

 

[Joe4]

Apologies A1 =“None” and A2 <> “-“ then bad

OK, that is what I assumed, so my formula should still be valid.
Give it a try and see if it does what you want.

 

[Ekguy41]

Thanks for the reply. I’m struggling to get it to work. Thought of doing it another way and break down the variables.
if A1 =“Good” and B2 <>”-“ and date in B2 is greater than today then ”Good” else “Bad
hope that makes sense

 

[Joe4]

Try this then:

=IF(AND(A1="Good",B2>TODAY()),"Good","Bad")

Note that the date in B2 MUST be entered as a valid date, and not entered as text!

If that still does not work, please post a sample of your data and expected results for each one.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.