Conditional Formatting - Overdue Expiry Date

[hamistasty]

I'm after a conditional format forumla that checks each value of a cell in a column and shows the word EXPIRED (and highlighted red) if the current date is past the date entered, and shows the word RENEW (and highlighted blue) if the current date is within 30 days of the date in the cell. Also the cell shows the input date and is highlighted green if the current date is before 30 days of the input date in the cell.

Thanks!

 

[AlphaFrog]

Conditional Formatting can't return a Value. It only can change the cells's format based on a condition.

In the example below, column B has IF formulas to return "Expired", "Renew", or "Current" based on the dates in column A.

The conditional Formatting changes the background colors to Red if Expired or Blue if within 30 days. The default background color is set as green.

Excel Workbook
	A	B
1	May 18, 2011	Expired
2	June 18, 2011	Expired
3	July 22, 2011	Renew
4	August 19, 2011	Current
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =$A1	Abc
A1	2. / Formula is =$A1-30	Abc
B1	1. / Formula is =$A1	Abc
B1	2. / Formula is =$A1-30	Abc
A2	1. / Formula is =$A2	Abc
A2	2. / Formula is =$A2-30	Abc
B2	1. / Formula is =$A2	Abc
B2	2. / Formula is =$A2-30	Abc
A3	1. / Formula is =$A3	Abc
A3	2. / Formula is =$A3-30	Abc
B3	1. / Formula is =$A3	Abc
B3	2. / Formula is =$A3-30	Abc
A4	1. / Formula is =$A4	Abc
A4	2. / Formula is =$A4-30	Abc
B4	1. / Formula is =$A4	Abc
B4	2. / Formula is =$A4-30	Abc

 

[hamistasty]

Thanks. How would I add IF(A1="N/A","N/A")
edit: it's not letting me post the code blah

 

[hunch]

Conditional Formatting can't return a Value. It only can change the cells's format based on a condition.

In the example below, column B has IF formulas to return "Expired", "Renew", or "Current" based on the dates in column A.

The conditional Formatting changes the background colors to Red if Expired or Blue if within 30 days. The default background color is set as green.

Excel Workbook
	A	B
1	May 18, 2011	Expired
2	June 18, 2011	Expired
3	July 22, 2011	Renew
4	August 19, 2011	Current
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =$A1	Abc
A1	2. / Formula is =$A1-30	Abc
B1	1. / Formula is =$A1	Abc
B1	2. / Formula is =$A1-30	Abc
A2	1. / Formula is =$A2	Abc
A2	2. / Formula is =$A2-30	Abc
B2	1. / Formula is =$A2	Abc
B2	2. / Formula is =$A2-30	Abc
A3	1. / Formula is =$A3	Abc
A3	2. / Formula is =$A3-30	Abc
B3	1. / Formula is =$A3	Abc
B3	2. / Formula is =$A3-30	Abc
A4	1. / Formula is =$A4	Abc
A4	2. / Formula is =$A4-30	Abc
B4	1. / Formula is =$A4	Abc
B4	2. / Formula is =$A4-30	Abc

Thanks Alfafrog, you just sorted out my problem which was a variation on this theme.

I wondered why I couldn't get an IF statement to do what I expected in a conditional format. I hadn't realised conditional formats uses a different syntax to the spreadsheet formula. I've been struggling with this one for most of the day!

 

[AlphaFrog]

I'm glad you were able to solve your problem.

I hadn't realised conditional formats uses a different syntax to the spreadsheet formula.

Just to clarify a bit, it's not so much a syntax difference. Conditional Format is expecting a formula result of either TRUE or FALSE. So in the case above...
=$A1 < TODAY()
..will return TRUE or FALSE. You could use this formula in a cell and get the same TRUE or FALSE result.