|
conditional formatting-Date
Hi,
I'm trying to use the conditional formatting feature with my Excel 2002 software for the first time, but I'm unsure of the values needed. Here's my question. I want a range of values (dates to be more specific) to be highlighted if the date listed is older than 10 business days (or 12 calendar days). I tried using the NOW, IF, and other fuctions, but didn't come across what I wanted to do. Any help? Thanks |
Calendar days are easy, just use
=TODAY()-A112 under formatconditional formatting formula is Regards, Peo Sjoblom "ivan" wrote: Hi, I'm trying to use the conditional formatting feature with my Excel 2002 software for the first time, but I'm unsure of the values needed. Here's my question. I want a range of values (dates to be more specific) to be highlighted if the date listed is older than 10 business days (or 12 calendar days). I tried using the NOW, IF, and other fuctions, but didn't come across what I wanted to do. Any help? Thanks |
Select the range of dates, e.g. A1:A100. Formatconditional
formattingformula is =TODAY()-A112 or =12 if you want. and select your color. "ivan" wrote in message ... Hi, I'm trying to use the conditional formatting feature with my Excel 2002 software for the first time, but I'm unsure of the values needed. Here's my question. I want a range of values (dates to be more specific) to be highlighted if the date listed is older than 10 business days (or 12 calendar days). I tried using the NOW, IF, and other fuctions, but didn't come across what I wanted to do. Any help? Thanks |
Hi!
Business days = Monday thru Friday less any holidays? This requires the Analysis ToolPak add-in to use the NETWORKDAYS function. If you consider holidays as not being business days you have to create a list of those dates and include the reference to that list in the NETWORKDAYS function. Assume that list is in the range G1:G10. Select the range of dates to conditional format. Conditional formatting Formula is: =AND(A1<TODAY()-12,NETWORKDAYS(A1,TODAY (),G1:G10)10) Select your format styles. OK out Biff -----Original Message----- Hi, I'm trying to use the conditional formatting feature with my Excel 2002 software for the first time, but I'm unsure of the values needed. Here's my question. I want a range of values (dates to be more specific) to be highlighted if the date listed is older than 10 business days (or 12 calendar days). I tried using the NOW, IF, and other fuctions, but didn't come across what I wanted to do. Any help? Thanks . |
You can't use ATP functions directly in conditional formatting since it is
an add-in and not located on the same sheet, only way would be to put the formula somewhere out of view and refer to it from the conditional formatting, assume you put the formula in AZ1 then in conditional formatting in cell A1 use =AZ1=TRUE -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Biff" wrote in message ... Hi! Business days = Monday thru Friday less any holidays? This requires the Analysis ToolPak add-in to use the NETWORKDAYS function. If you consider holidays as not being business days you have to create a list of those dates and include the reference to that list in the NETWORKDAYS function. Assume that list is in the range G1:G10. Select the range of dates to conditional format. Conditional formatting Formula is: =AND(A1<TODAY()-12,NETWORKDAYS(A1,TODAY (),G1:G10)10) Select your format styles. OK out Biff -----Original Message----- Hi, I'm trying to use the conditional formatting feature with my Excel 2002 software for the first time, but I'm unsure of the values needed. Here's my question. I want a range of values (dates to be more specific) to be highlighted if the date listed is older than 10 business days (or 12 calendar days). I tried using the NOW, IF, and other fuctions, but didn't come across what I wanted to do. Any help? Thanks . |
Good catch!
Another way, use a named formula. Biff -----Original Message----- You can't use ATP functions directly in conditional formatting since it is an add-in and not located on the same sheet, only way would be to put the formula somewhere out of view and refer to it from the conditional formatting, assume you put the formula in AZ1 then in conditional formatting in cell A1 use =AZ1=TRUE -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Biff" wrote in message ... Hi! Business days = Monday thru Friday less any holidays? This requires the Analysis ToolPak add-in to use the NETWORKDAYS function. If you consider holidays as not being business days you have to create a list of those dates and include the reference to that list in the NETWORKDAYS function. Assume that list is in the range G1:G10. Select the range of dates to conditional format. Conditional formatting Formula is: =AND(A1<TODAY()-12,NETWORKDAYS(A1,TODAY (),G1:G10)10) Select your format styles. OK out Biff -----Original Message----- Hi, I'm trying to use the conditional formatting feature with my Excel 2002 software for the first time, but I'm unsure of the values needed. Here's my question. I want a range of values (dates to be more specific) to be highlighted if the date listed is older than 10 business days (or 12 calendar days). I tried using the NOW, IF, and other fuctions, but didn't come across what I wanted to do. Any help? Thanks . . |
| All times are GMT +1. The time now is 05:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com