Excel 2007 : Conditional Formatting Cells that contain formulas not working as intended

**markharper80** · 04-28-2011, 04:14 PM

Good day all,

I have two sets of conditional formating of cell colors for a single colum.

The first (which works as intended) turn the cell blue when within a certain date range. This date range is calculated based on adding a number of days to todays date.

The problem is that sometimes no date will be inputed in the refence cell for the formula. In this case the cell spits out a result for the formula of 2/4/1900. So an idea for a work around was to add a second conditional formatting rule that whenever this text was present (2/4/1900 resulting from not have a dates in the refence cells) the text would turn white along with the cell fill effectivly 'whiting out' the 2/4/1900 until a date was inputted in the refence cell.

However, this desired effect does not happen. I would guess it has something to do with the fact that when you select the field in question, you do not see actual text but the formula. Is there a way not to see the erroneous date of 2/4/1900 when there is no date in the refence cell or get this conditional formatting to work?

Thank you again.

**ChemistB** · 04-28-2011, 04:38 PM

Can you upload an example speadsheet with cells that are set up the way you describe? (Go Advanced>manage attachments) It's hard to follow without the spreadsheet.
If you can't do that, the actual conditional formula would be helpful.

2/4/1900 corresponds to 35, Can you try that i.e.
=A2=35 Format as white text

**Cutter** · 04-28-2011, 04:39 PM

Why don't you change the formulas to test for the date and if it's not there return a blank?

Or

Put CF in place to trigger if the cell value is less than an arbitrary number like 1000.

**TMS** · 04-28-2011, 04:44 PM

You could use an AND condition. You don't specify the cells in question so it's difficult to offer a definitive answer ... but, something like:

=AND(refcell<>"", your date formula (which works))

Regards

**markharper80** · 04-28-2011, 06:17 PM

Here is the dummy book...basically G4 is a bogus number since no date is input in D4. If there is a way to show G4 as a blank when no date is input in D4 then that would be preferable while still keepign the formula intact as eventually a date will be added to D4. But I have not been able to figure that out so instead I have added a conditional formating to 'white it out'. But the white out is not working.

**daddylonglegs** · 04-28-2011, 06:39 PM

Following TMShucks suggestion, try this formula in G4 copied down

=IF(D3="","",D3+35)

Note that it's not "good practice" to use formulas like you have in F3, i.e. =TODAY()-C:C
That might work but it's better to use the specific row number, i.e.

=TODAY()-C3

**markharper80** · 04-29-2011, 09:11 AM

Thanks all. =IF(D3="","",D3+35) worked like a charm. I have never used the IF function before in this way, its so simple and so effective. Great stuff!