Conditional format with date range. Sanity Check.

[blbat]

Folks, I'm looking for a Sanity check on my date-based conditional formats.

if you find the logic flawed, or you have a better way of doing this, I would be grateful for your input.

I have two columns of dates as shown in table:

Excel Workbook
	M	N
1	Certification Date	Projected Re-certification Date
2	09/28/10	09/27/12
3	09/30/10	09/29/12
4
5
6	07/26/08	07/26/10
7
8
9	02/14/11	02/13/13
10	09/30/10	09/29/12
11
12	05/20/09	05/20/11
13
Data

Excel 2007

Cell Formulas
Range	Formula
N2	=IF($M2="","",$M2+730)

Date in column "M" is input by user. Date in column "N" is calculated by formula as shown.

My conditional formatting logic for column "N" is as follows:

Rule 1) Format only cells that contain BLANKS. No format set. STOP IF TRUE checked.

intended meaning; if Cell in column "M" is Blank, then corresponding cell in column "N" is left blank with NO formatting. STOP.

Rule 2) Format only cells that contain Cell Value between =TODAY() and =TODAY()+180
Cell background Format YELLOW. STOP IF TRUE checked.

intended meaning; if Column "N" date falls within todays date and the previous six months then turn the cell background YELLOW. STOP.

Rule 3) Use a formula to determine which cells to format. Format values where this formula is true =$N2< TODAY()

intended meaning; if cell value does not meet the first two rules criteria AND its less than TODAY(), it's in the past and is red. STOP.

Rule 4) Use a formula to determine which cells to format. Format values where this formula is true: =$N2>=TODAY()-180 Format cell background GREEN.

intended meaning; if cell value does not meet the first three rules criteria it is GREEN because it's not in the past, and it's not within six months of the given date.

Thanks.

 

[venkat1926]

see the sheet below

highlight N2
under conditions formatting(format menu-conditional foramtting)
condition 1
the formula is =AND(N2>=TODAY(),N2<=TODAY()+180)
in the formatting window click format in the middle row and click "Pattern" and choose yellow and click ok in the format cell window. Do not ok main window but click "add"

condition2 is
formula is =AND(N2<TODAY(),N2>0)
and in pattern click red
click ok and in main window clik add

condition3 is
formula is =N2>TODAY()+180
and color this red. click ok in the format cells window and also click ok in the main window

now highlight N2 - click edit-copy
highlight N3 to the end of column N data
click edit-pastespecial-format
click ok

the sheet will be like thisl

Excel Workbook
	M	N
1	Certification Date	Projected Re-certification Date
2	9/28/2010	9/27/2012
3	9/30/2010	9/29/2012
4
5
6	7/26/2008	7/26/2010
7
8
9	2/14/2011	2/13/2013
10	9/30/2010	9/29/2012
11
12	5/20/2009	5/20/2011
Sheet1

 

[blbat]

Thanks for the response venkat1926.
I truly appreciate your efforts.

I see the logic of your first condition, but I'm curious about your condition 2 criteria.
It reads as: formula is =AND(N20) and you want it set to RED.
Does this mean you are using a static cell reference in the same column as we are applying the conditional formatting to? and why cell N20?

for Condition 3 you also want have this set to RED..I'm not sure I understand the logic.

I apologize for not being clearer in my original post...the conditional formatting I have works...what I was after was a check on the logic I used for my formulas, and if there was a better way of writing the formulas.

I will upload some screen shots of my conditional formatting dialog boxes in the hope it will make more sense!

 

[blbat]

Here is a screenshot of the rule order. NOTE: Stop if True checked.

 

[blbat]

here are the screenshots of the individual rules:

 

[venkat1926]

sorry some typing mistakes in my posting.
others have given better solutions

I must have been in sleeping mood.
<today(),n2><today(),n2><today(),n2><today(),n2><today(),n2> </today(),n2></today(),n2></today(),n2></today(),n2></today(),n2>