Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WORKDAY | Excel Worksheet Functions | |||
WORKDAY() and probably more | New Users to Excel | |||
Workday Help | Excel Discussion (Misc queries) | |||
Workday function | Excel Worksheet Functions | |||
How do I get the WORKDAY function? | Excel Discussion (Misc queries) |