Conditional formatting dates



I have 1 spread sheet that has an expiration date entered on it. The
information from that sheet is fed into another sheet that calculates 9
months after the expiration date on the 1st sheet.

Sheet 1 Sheet 2

A B(expiration date) A
B(next contact date)
client name 3/5/10 same client name 12/5/10

The problem is on Sheet 2, because the column is formatted for date and
there is a link to sheet 1, it is showing up as 10/1/00, until an expiration
date is inserted in sheet 1. I've tried conditional formatting using the
number equivalent and making the font white to hide it - doesn't seem to work.

Any ideas would be greately appreciated. Thanks - Cindyt

Luke M

Try conditional format of:
Cell value, is less than, 1000
Format white font, white cell

Or, change your link to have an IF statement like:
=IF('Sheet1'!A1 = "","",'Sheet1'!A1)

Chip Pearson

You can't do it with conditional formatting. You can write the formula
on Sheet2 to display an empty string if the cell to which it is linked
is empty. For example, on Sheet2,


This will return the value in Sheet1 A1 is it has content, or return
an empty string if Sheet1 A1 is empty.

Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question