I'm trying to have Excel tell me if our Techs are taking too long for service
calls using conditional formatting. I have an xls that has the date a call
was made to us so one of our techs goes out to their home. We have up to 5
days to respond to a service call. (Since Conditional Formatting only allows
for 3 conditions this seems a bit tough for me) If enter today's date 8/12 i
want excel to hilight that row green for the next two days including today.
At the point when it becomes 3 to 4 days after i want it to highlight yellow
and when it becomes the last day to respond (5th day) i want it to highlight
red. i have a column where i will "X" if the job has been done; if it has i
want the conditional formatting to not do antyhing...are these too many
conditions?

Please help - Excel UNGURU

One thing i forgot to mention these can only be weekdays.

"Daniel Q." wrote:

I'm trying to have Excel tell me if our Techs are taking too long for service
calls using conditional formatting. I have an xls that has the date a call
was made to us so one of our techs goes out to their home. We have up to 5
days to respond to a service call. (Since Conditional Formatting only allows
for 3 conditions this seems a bit tough for me) If enter today's date 8/12 i
want excel to hilight that row green for the next two days including today.
At the point when it becomes 3 to 4 days after i want it to highlight yellow
and when it becomes the last day to respond (5th day) i want it to highlight
red. i have a column where i will "X" if the job has been done; if it has i
want the conditional formatting to not do antyhing...are these too many
conditions?

Please help - Excel UNGURU

Three conditions will do it

Condition 1 formula: =NETWORKDAYS(A4,TODAY())=5
Condition 2 formula: =NETWORKDAYS(A4,TODAY())=3
Condition 3 formula: =NETWORKDAYS(A4,TODAY())=1

--
HTH

Bob

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

"Daniel Q." wrote in message
...
I'm trying to have Excel tell me if our Techs are taking too long for
service
calls using conditional formatting. I have an xls that has the date a
call
was made to us so one of our techs goes out to their home. We have up to
5
days to respond to a service call. (Since Conditional Formatting only
allows
for 3 conditions this seems a bit tough for me) If enter today's date
8/12 i
want excel to hilight that row green for the next two days including
today.
At the point when it becomes 3 to 4 days after i want it to highlight
yellow
and when it becomes the last day to respond (5th day) i want it to
highlight
red. i have a column where i will "X" if the job has been done; if it has
i
want the conditional formatting to not do antyhing...are these too many
conditions?

Please help - Excel UNGURU

On Wed, 13 Aug 2008 08:26:22 +0100, "Bob Phillips"
wrote:

Three conditions will do it

Condition 1 formula: =NETWORKDAYS(A4,TODAY())=5
Condition 2 formula: =NETWORKDAYS(A4,TODAY())=3
Condition 3 formula: =NETWORKDAYS(A4,TODAY())=1

--
HTH

Bob

Bob,

Will NETWORKDAYS work in conditional formatting in pre-2007 versions?
--ron

No you cannot, however one could put these 3 formulas somewhere off the
visible sheet like in IV1:IV3 or wherever
then use formula is and


condition1 =$IV$1
condition2 =$IV$2
condition3 =$IV$3


then it will work

--


Regards,


Peo Sjoblom

"Ron Rosenfeld" wrote in message
...
On Wed, 13 Aug 2008 08:26:22 +0100, "Bob Phillips"
wrote:

Three conditions will do it

Condition 1 formula: =NETWORKDAYS(A4,TODAY())=5
Condition 2 formula: =NETWORKDAYS(A4,TODAY())=3
Condition 3 formula: =NETWORKDAYS(A4,TODAY())=1

--
HTH

Bob

Bob,

Will NETWORKDAYS work in conditional formatting in pre-2007 versions?
--ron

I have xl2003, and NETWORKDAYS does not work in conditional formatting.
--
John C


"Ron Rosenfeld" wrote:

On Wed, 13 Aug 2008 08:26:22 +0100, "Bob Phillips"
wrote:

Three conditions will do it

Condition 1 formula: =NETWORKDAYS(A4,TODAY())=5
Condition 2 formula: =NETWORKDAYS(A4,TODAY())=3
Condition 3 formula: =NETWORKDAYS(A4,TODAY())=1

--
HTH

Bob

Bob,

Will NETWORKDAYS work in conditional formatting in pre-2007 versions?
--ron

Condition1(red):
Formula is: =AND($B4<"x",(TODAY-$A4-2*(WEEKDAY($A4)2))4)
Condition2(yellow):
Formula is: =AND($B4<"x",(TODAY-$A4-2*(WEEKDAY($A4)3))2)
Condition3(green):
=$B4<"x"

This assumes column A is the date of the service call, and column B is the
column where an x is entered if the service call is complete. It also assumes
that first row of service call data is row 4.


--
John C


"Daniel Q." wrote:

I'm trying to have Excel tell me if our Techs are taking too long for service
calls using conditional formatting. I have an xls that has the date a call
was made to us so one of our techs goes out to their home. We have up to 5
days to respond to a service call. (Since Conditional Formatting only allows
for 3 conditions this seems a bit tough for me) If enter today's date 8/12 i
want excel to hilight that row green for the next two days including today.
At the point when it becomes 3 to 4 days after i want it to highlight yellow
and when it becomes the last day to respond (5th day) i want it to highlight
red. i have a column where i will "X" if the job has been done; if it has i
want the conditional formatting to not do antyhing...are these too many
conditions?

Please help - Excel UNGURU