Conditional formatting: If text or If date?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

I have a spreadsheet that tracks reports. In a far right-hand column,
I have a cell for when a re-write is submitted. I put an "X" in the
cell when a re-write is needed, and the date submitted when it's
turned in.

I'd like to put some conditional formatting on the report number cell
so it becomes one color if there's an "X" in the re-write cell, and
another color if there's a date in the re-write cell. Is this
possible?

Ed
 
Select report number cell
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =M1="X"
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =ISDATE(N1)
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Change M1 and N1 to suuit

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob:

I'm in XL2003. I don't seem to have an ISDATE function??? I have the
Analysis toolpack loaded.

Ed
 
Nor do I <g>

I should have said

Add a formula of =IsADate(N1)

and included that function

Function IsADate(inDate)
IsADate = IsDate(inDate)
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I bet Bob created his own user defined function called =isdate().

But you may be able to use this formula instead:

=AND(ISNUMBER(n1),LEFT(CELL("format",n1),1)="D")
 
Thanks for all the input. Between all of this, I have managed to get
up and running.

Appreciate the boost.
Ed
 
Dave Peterson said:
I bet Bob created his own user defined function called =isdate().

But you may be able to use this formula instead:

=AND(ISNUMBER(n1),LEFT(CELL("format",n1),1)="D")
Click to expand...

That's quite clever.
 
Back
Top