Conditional formatting a complex condition - Need help creating the formula

[Nebulous]

Hello. Please reference the following table for my comments:

-- removed inline image ---

​

• Request Date (O) will always contain a date
• Original Promise Date (M) may be blank or it may contain a date
• After Original Promise Date is populated, at some point Current Promise Date (N) will be given a date

I need to determine whether a Request Date is earlier than our Promise Date according to the following:

• If $N3 contains data, shade $O1 red if it is less than $N3
• If $N3 is blank and $M3 contains data, shade $O3 red if it is less than $M3
• If $M3 is blank, do nothing

I need this same logic applied to all rows that have data in Column O, not just Row 3.

Can you please help me build this Conditional Formatting Rule? Thanks in advance!

~N

 

[Nebulous]

I guess I'm not allowed to post a screenshot of my spreadsheet here. I'm attempting to recreate the gist of it as a table below for reference:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Original Promise Date[/TD]
[TD="align: center"]Current Promise Date
[/TD]
[TD="align: center"]Request Date
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/16/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]11/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/2/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]7/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/2/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]8/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]7/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]8/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/2/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]9/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]10/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]9/2/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]1/3/17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]12/3/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD]11/25/16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Is this what you mean?
I also assume do nothing if O column cell is blank.

Excel 2010
	M	N	O
1	Original Promise Date	Current Promise Date	Request Date
2
3	10/16/2016	11/3/2016	10/2/2016
4	7/3/2016	10/3/2016	10/3/2016
5	10/2/2016
6	8/3/2016	7/3/2016	8/3/2016
7
8	10/2/2016
9	9/3/2016	10/3/2016	9/2/2016
10	1/3/2017	12/3/2016	11/25/2016
Sheet1

Conditional Formatting formula:

=IF(AND(O3<>"",M3<>""),OR(O3<M3,O3<N3),0)

Copy formatting down Column O with format painter.

 

[Nebulous]

@jtakw - Not quite. [Note: I closed your formula with two extra parens].

I want Column O highlighted only when its date is earlier than the date in Column N (or earlier than the date in Column M if N is blank).

I tested your formula on cells with blanks and cells with data. Here's what happened:
- If Column M had any data at all - whether earlier, later, or equal to Column O - it highlighted Column O
- If Column M had no data it didn't highlight.

Note: "Request Date" in Column O will always have data. Leaving it out was my error when I tried to recreate the table.

Suggestions? And thanks for the attention to this!

~N

 

[jtakw]

Sorry, formula got cut off....it's supposed to be:

=IF(AND(O3<>"",M3<>""),OR(O3< M3,O3< N3),0)

Same instructions as before.

If you click Reply with Quote to my post #3, you'll see the entire formula within the quote, this forum has problems cutting off postings involving the < sign.

 

[jtakw]

@jtakw
Note: "Request Date" in Column O will always have data. Leaving it out was my error when I tried to recreate the table.

Suggestions? And thanks for the attention to this!

~N

In that case, just use:

=IF(M3<>"",OR(O3< M3,O3< N3),0)

 

[Nebulous]

Perfect. Thank you!

 

[jtakw]

Perfect. Thank you!

You're welcome.