Hi all,

I have dates in columns L & K, in column M I calculate the difference
between these two dates.

Valid number of days btwn dates would be 1 to 100. If the difference is
less than 1 day or is more than 100 days, I want to flag the cells somehow.

I have tried:
Under Format Cells General Custom,
[Red][<1]General;[Blue][100]General
- this works great but the valid numbers disappear (i.e., numbers from 1 to
100).

I tried
[Red][<1]General;[Blue][100]General;[Black][0]General;[Black][<101]General
- and I get the error "Excel cannot use the number format you typed"

Alternatively, I would be happy to do conditional formatting for the cells
with values less than 1 to be pink and above 100 to be green. I can do this
and the numbers all show up but then the invalid numbers (like -39191) show
up too and it's ugly. Is there a way to have the conditional formatting color
show for cells outside of the range but the cell is left blank?

tia for any suggestions,
karen

sorry forgot to mention working in Excel 2000 on stand alone XP

"klam" wrote:

Hi all,

I have dates in columns L & K, in column M I calculate the difference
between these two dates.

Valid number of days btwn dates would be 1 to 100. If the difference is
less than 1 day or is more than 100 days, I want to flag the cells somehow.

I have tried:
Under Format Cells General Custom,
[Red][<1]General;[Blue][100]General
- this works great but the valid numbers disappear (i.e., numbers from 1 to
100).

I tried
[Red][<1]General;[Blue][100]General;[Black][0]General;[Black][<101]General
- and I get the error "Excel cannot use the number format you typed"

Alternatively, I would be happy to do conditional formatting for the cells
with values less than 1 to be pink and above 100 to be green. I can do this
and the numbers all show up but then the invalid numbers (like -39191) show
up too and it's ugly. Is there a way to have the conditional formatting color
show for cells outside of the range but the cell is left blank?

tia for any suggestions,
karen