WORKDAY

  • Thread starter Thread starter Daniel Q.
  • Start date Start date
D

Daniel Q.

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)
 
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
Click to expand...

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
 
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.
 
Back
Top