WORKDAY
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 |
WORKDAY
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 |
WORKDAY
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 |
WORKDAY
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 |
WORKDAY
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 |
WORKDAY
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 |
WORKDAY
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 |
All times are GMT +1. The time now is 06:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com