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

[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)

[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

[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.

"Pete_UK" <pashurst@auditel.net> wrote in message
news:1142184866.120312.262910@i40g2000cwc.googlegroups.com...
>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
>

[Ray Stevens]

Oops... to the criteria below, add:
4. Category NOT = "Parking Lot"

"Ray Stevens" <nfr@nospam.com> wrote in message
news:%23NIxxHgRGHA.1096@TK2MSFTNGP11.phx.gbl...
> 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.
>
> "Pete_UK" <pashurst@auditel.net> wrote in message
> news:1142184866.120312.262910@i40g2000cwc.googlegroups.com...
>>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
>>
>
>

[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




"Ray Stevens" <nfr@nospam.com> wrote in message
news:%23NIxxHgRGHA.1096@TK2MSFTNGP11.phx.gbl...
> 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.
>
> "Pete_UK" <pashurst@auditel.net> wrote in message
> news:1142184866.120312.262910@i40g2000cwc.googlegroups.com...
>>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
>>
>
>

[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.


"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:eUu7cLhRGHA.4572@TK2MSFTNGP10.phx.gbl...
> =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
>
>
>
>
> "Ray Stevens" <nfr@nospam.com> wrote in message
> news:%23NIxxHgRGHA.1096@TK2MSFTNGP11.phx.gbl...
>> 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.
>>
>> "Pete_UK" <pashurst@auditel.net> wrote in message
>> news:1142184866.120312.262910@i40g2000cwc.googlegroups.com...
>>>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
>>>
>>
>>
>

[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.


"Pete_UK" <pashurst@auditel.net> wrote in message
news:1142184866.120312.262910@i40g2000cwc.googlegroups.com...
>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
>