How do I do a complex conditional in a conditional formatting formula

R

Ray Stevens

I am attempting to compare multiple fields in a conditional formatting
formulat but am not sure how. For example, the following generates an error
when I attempt to save it:

=IF($F2="",,TEXT(TODAY() and $L2="PSR","yyyy-mm-dd")>$F2)
 
D

davesexcel

Ray said:
I am attempting to compare multiple fields in a conditional formatting
formulat but am not sure how. For example, the following generates an
error
when I attempt to save it:

=IF($F2="",,TEXT(TODAY() and $L2="PSR","yyyy-mm-dd")>$F2)
Click to expand...
WHAT IS THIS FORMULA SUPPOSED TO DO??
 
P

Pete_UK

I don't understand what condition you are trying to set up. It looks
like you want $L2 to be equal to "PSR" and $F2 is not blank, but where
does TODAY( ) come into it?

Please re-post with an explanation of what you want to do.

Pete
 
R

Ray Stevens

In this case I am looking to turn a row red if a text "date field" for an
agreed upon date is less than the current date and the column call type is
"PSR". The data is populated from a SQL Server stored procedure call where
all dates, unfortunately, are varchar(10) formatted as yyyy-mm-dd.

Ultimately, the row color will be determined by a number of criteria:

1. CallType = "PSR"
2. Status = "Open" or "Reopened"
3. Agreed date less than todays date. (i.e., the PSR is late).

PSR's with no agreed date will be another color and PSR's approaching their
agreed date within a specified period (say, a few days) and still open will
be yet another.
 
R

Ray Stevens

Actually, the pseudo-code for what I am attempting in the conditional
formatting is something like this:

If (STATUS = "Open" or "Reopened") AND (CATEGORY not = "Parking Lot") AND
(CALLTYPE = "PSR")
IF AGREED_DATE and NEEDED_BY_DATE = BLANK
ROW COLOR = BLUE
ELSE
IF AGREED_DATE = BLANK
TEST_DATE = NEEDED_BY_DATE
ELSE
TEST_DATE = AGREED_DATE
END_IF
IF TODAYS_DATE > TEST_DATE
ROW COLOR = RED
END_IF
END_IF
END_IF

NOTE: All date fields are populated from SQL Server as varchar(10)
yyyy-mm-dd.
 
P

Peo Sjoblom

=AND($F2<TODAY(),$L2="PSR")

if you want to use conditional formatting the whole idea if you want to use
formulas is to have the formulas return TRUE or FALSE, no need for IF
functions at all Having said that I believe you haven't really explained so
we can understand so I don't know if the above is of any help, but it is a
basic way of setting up a condition

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
R

Ray Stevens

I realized I wasn't clear as to my ultimate goal and expanded upon it in my
response to Pete_UK. The following is what I posted to him:

Actually, the pseudo-code for what I am attempting in the conditional
formatting is something like this:

If (STATUS = "Open" or "Reopened") AND (CATEGORY not = "Parking Lot") AND
(CALLTYPE = "PSR")
IF AGREED_DATE and NEEDED_BY_DATE = BLANK
ROW COLOR = BLUE
ELSE
IF AGREED_DATE = BLANK
TEST_DATE = NEEDED_BY_DATE
ELSE
TEST_DATE = AGREED_DATE
END_IF
IF TODAYS_DATE > TEST_DATE
ROW COLOR = RED
END_IF
END_IF
END_IF

NOTE: All date fields are populated from SQL Server as varchar(10)
yyyy-mm-dd.
 
Top