| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
>Would you be able to test it again for me just to make sure
Worked ok for me. Here' what I did: Defined WorkDate as =WORKDAY(TODAY(),14) Applied the conditional formatting: Selected cell AI8 Format>Conditional Formatting Formula Is: =$AI$8>WorkDate Set the fill color to green (any color will do, I just like green!) In AI8 I entered 12/31/2008 and it turned green as expected. =WORKDAY(TODAY(),14) evaluates to 7/21/2008 So, the format is applied when AI8 > 7/21/2008 (based on today's date). In AI8 I entered 7/20/2008 and the green format was removed. In AI8 I entered 7/25/2008 and the green format was again applied. -- Biff Microsoft Excel MVP "Mike" <(E-Mail Removed)> wrote in message news  CC03ED0-48E9-43BB-81AB-(E-Mail Removed)...> Hi T, it seems so straight forward but I can't get this to work in the > Conditional Formatting - highlight cell in red. I have tried testing it > using > a blank spreadsheet with a date >14 days old in cell A1 and created the > Name > as you suggested and retryed but no luck. No errors occur which is a > bonus. > > Would you be able to test it again for me just to make sure > > Thanks again for all your help > > -- > Regards > Mike > > > "T. Valko" wrote: > >> The WORKDAY function is part of the analysis ToolPak add-in which is >> another >> file. That's why you get the message. >> >> Try this: >> >> Create a defined named formula. >> Goto Insert>Name>Define >> Name: WorkDate >> Refers to: =WORKDAY(TODAY(),14) >> OK >> >> Then, use this formula in the conditional formatting: >> >> =$AI$8>WorkDate >> >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Mike" <(E-Mail Removed)> wrote in message >> news:F0F7247B-7D8E-47A9-B442-(E-Mail Removed)... >> > Hi Bob, thanks for the quick reply back. However, I get a message as >> > soon >> > as >> > I try to save it 'You may not use references from other worksheets for >> > conditional fformatting criteria' >> > >> > Any clues on this? >> > -- >> > Regards >> > Mike >> > >> > >> > "Bob Phillips" wrote: >> > >> >> =$AI$8>WORKDAY(TODAY(),14) >> >> >> >> are you sure that you mean 14 working days, not 10? >> >> >> >> -- >> >> HTH >> >> >> >> Bob >> >> >> >> (there's no email, no snail mail, but somewhere should be gmail in my >> >> addy) >> >> >> >> "Mike" <(E-Mail Removed)> wrote in message >> >> news:B05052E1-BC5B-4DE7-91A0-(E-Mail Removed)... >> >> > Hi all, hope you can help. Excel 2003 >> >> > >> >> > I need to use conditional formatting on a cell if the date in this >> >> > cell >> >> > is >> >> > 14 working days > Today(). I'm not sure where to place the >> >> > (AI1-Workday) >> >> > part within the formula bar >> >> > >> >> > =TODAY()-$AI$8>14 >> >> > -- >> >> > Regards >> >> > Mike >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Hi T, I just realised what I did wrong. I misinterpretted the formula with
regards to the dates. It's the other way round. I want the cell in AI8 to format in Red if the date is 14 working days BEHIND(<) Todays date. I tried modifying the WorkDate name WORKDAY(TODAY(),14) but it does not like when I use < sign in it. I also used =$AI$8<WorkDate but does not work out. Can you help me again - I so appreciate your help in me learning Excel -- Regards Mike "T. Valko" wrote: > >Would you be able to test it again for me just to make sure > > Worked ok for me. Here' what I did: > > Defined WorkDate as =WORKDAY(TODAY(),14) > > Applied the conditional formatting: > > Selected cell AI8 > Format>Conditional Formatting > Formula Is: =$AI$8>WorkDate > Set the fill color to green (any color will do, I just like green!) > > In AI8 I entered 12/31/2008 and it turned green as expected. > > =WORKDAY(TODAY(),14) evaluates to 7/21/2008 > > So, the format is applied when AI8 > 7/21/2008 (based on today's date). > > In AI8 I entered 7/20/2008 and the green format was removed. > > In AI8 I entered 7/25/2008 and the green format was again applied. > > > -- > Biff > Microsoft Excel MVP > > > "Mike" <(E-Mail Removed)> wrote in message > news CC03ED0-48E9-43BB-81AB-(E-Mail Removed)...> > Hi T, it seems so straight forward but I can't get this to work in the > > Conditional Formatting - highlight cell in red. I have tried testing it > > using > > a blank spreadsheet with a date >14 days old in cell A1 and created the > > Name > > as you suggested and retryed but no luck. No errors occur which is a > > bonus. > > > > Would you be able to test it again for me just to make sure > > > > Thanks again for all your help > > > > -- > > Regards > > Mike > > > > > > "T. Valko" wrote: > > > >> The WORKDAY function is part of the analysis ToolPak add-in which is > >> another > >> file. That's why you get the message. > >> > >> Try this: > >> > >> Create a defined named formula. > >> Goto Insert>Name>Define > >> Name: WorkDate > >> Refers to: =WORKDAY(TODAY(),14) > >> OK > >> > >> Then, use this formula in the conditional formatting: > >> > >> =$AI$8>WorkDate > >> > >> > >> -- > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Mike" <(E-Mail Removed)> wrote in message > >> news:F0F7247B-7D8E-47A9-B442-(E-Mail Removed)... > >> > Hi Bob, thanks for the quick reply back. However, I get a message as > >> > soon > >> > as > >> > I try to save it 'You may not use references from other worksheets for > >> > conditional fformatting criteria' > >> > > >> > Any clues on this? > >> > -- > >> > Regards > >> > Mike > >> > > >> > > >> > "Bob Phillips" wrote: > >> > > >> >> =$AI$8>WORKDAY(TODAY(),14) > >> >> > >> >> are you sure that you mean 14 working days, not 10? > >> >> > >> >> -- > >> >> HTH > >> >> > >> >> Bob > >> >> > >> >> (there's no email, no snail mail, but somewhere should be gmail in my > >> >> addy) > >> >> > >> >> "Mike" <(E-Mail Removed)> wrote in message > >> >> news:B05052E1-BC5B-4DE7-91A0-(E-Mail Removed)... > >> >> > Hi all, hope you can help. Excel 2003 > >> >> > > >> >> > I need to use conditional formatting on a cell if the date in this > >> >> > cell > >> >> > is > >> >> > 14 working days > Today(). I'm not sure where to place the > >> >> > (AI1-Workday) > >> >> > part within the formula bar > >> >> > > >> >> > =TODAY()-$AI$8>14 > >> >> > -- > >> >> > Regards > >> >> > Mike > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Use:
WorkDate = WORKDAY(TODAY(),-14) And: =$AI$8<WorkDate -- Biff Microsoft Excel MVP "Mike" <(E-Mail Removed)> wrote in message news:FC1F61AF-ABE5-4B09-85A7-(E-Mail Removed)... > Hi T, I just realised what I did wrong. I misinterpretted the formula with > regards to the dates. It's the other way round. I want the cell in AI8 > to > format in Red if the date is 14 working days BEHIND(<) Todays date. > > I tried modifying the WorkDate name WORKDAY(TODAY(),14) but it does not > like > when I use < sign in it. I also used =$AI$8<WorkDate but does not work > out. > > Can you help me again - I so appreciate your help in me learning Excel > -- > Regards > Mike > > > "T. Valko" wrote: > >> >Would you be able to test it again for me just to make sure >> >> Worked ok for me. Here' what I did: >> >> Defined WorkDate as =WORKDAY(TODAY(),14) >> >> Applied the conditional formatting: >> >> Selected cell AI8 >> Format>Conditional Formatting >> Formula Is: =$AI$8>WorkDate >> Set the fill color to green (any color will do, I just like green!) >> >> In AI8 I entered 12/31/2008 and it turned green as expected. >> >> =WORKDAY(TODAY(),14) evaluates to 7/21/2008 >> >> So, the format is applied when AI8 > 7/21/2008 (based on today's date). >> >> In AI8 I entered 7/20/2008 and the green format was removed. >> >> In AI8 I entered 7/25/2008 and the green format was again applied. >> >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Mike" <(E-Mail Removed)> wrote in message >> news CC03ED0-48E9-43BB-81AB-(E-Mail Removed)...>> > Hi T, it seems so straight forward but I can't get this to work in the >> > Conditional Formatting - highlight cell in red. I have tried testing it >> > using >> > a blank spreadsheet with a date >14 days old in cell A1 and created the >> > Name >> > as you suggested and retryed but no luck. No errors occur which is a >> > bonus. >> > >> > Would you be able to test it again for me just to make sure >> > >> > Thanks again for all your help >> > >> > -- >> > Regards >> > Mike >> > >> > >> > "T. Valko" wrote: >> > >> >> The WORKDAY function is part of the analysis ToolPak add-in which is >> >> another >> >> file. That's why you get the message. >> >> >> >> Try this: >> >> >> >> Create a defined named formula. >> >> Goto Insert>Name>Define >> >> Name: WorkDate >> >> Refers to: =WORKDAY(TODAY(),14) >> >> OK >> >> >> >> Then, use this formula in the conditional formatting: >> >> >> >> =$AI$8>WorkDate >> >> >> >> >> >> -- >> >> Biff >> >> Microsoft Excel MVP >> >> >> >> >> >> "Mike" <(E-Mail Removed)> wrote in message >> >> news:F0F7247B-7D8E-47A9-B442-(E-Mail Removed)... >> >> > Hi Bob, thanks for the quick reply back. However, I get a message as >> >> > soon >> >> > as >> >> > I try to save it 'You may not use references from other worksheets >> >> > for >> >> > conditional fformatting criteria' >> >> > >> >> > Any clues on this? >> >> > -- >> >> > Regards >> >> > Mike >> >> > >> >> > >> >> > "Bob Phillips" wrote: >> >> > >> >> >> =$AI$8>WORKDAY(TODAY(),14) >> >> >> >> >> >> are you sure that you mean 14 working days, not 10? >> >> >> >> >> >> -- >> >> >> HTH >> >> >> >> >> >> Bob >> >> >> >> >> >> (there's no email, no snail mail, but somewhere should be gmail in >> >> >> my >> >> >> addy) >> >> >> >> >> >> "Mike" <(E-Mail Removed)> wrote in message >> >> >> news:B05052E1-BC5B-4DE7-91A0-(E-Mail Removed)... >> >> >> > Hi all, hope you can help. Excel 2003 >> >> >> > >> >> >> > I need to use conditional formatting on a cell if the date in >> >> >> > this >> >> >> > cell >> >> >> > is >> >> >> > 14 working days > Today(). I'm not sure where to place the >> >> >> > (AI1-Workday) >> >> >> > part within the formula bar >> >> >> > >> >> >> > =TODAY()-$AI$8>14 >> >> >> > -- >> >> >> > Regards >> >> >> > Mike >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Hi Biff, that works great thanks! I've noticed however that when I apply
this formula/formatting to other cells below which do not have yet dates inserted into them, they automatically show up in Red formatting ( which suggest the formula is working). However, this causes confusion during monitoring of the spreadsheet. Therefore, can I add a IF statement in here to say "If the cell is blank, do not format the cell in Red but leave unformatted". Is this possible?, your help to me is most grateful -- Regards Mike "T. Valko" wrote: > Use: > > WorkDate = WORKDAY(TODAY(),-14) > > And: > > =$AI$8<WorkDate > > -- > Biff > Microsoft Excel MVP > > > "Mike" <(E-Mail Removed)> wrote in message > news:FC1F61AF-ABE5-4B09-85A7-(E-Mail Removed)... > > Hi T, I just realised what I did wrong. I misinterpretted the formula with > > regards to the dates. It's the other way round. I want the cell in AI8 > > to > > format in Red if the date is 14 working days BEHIND(<) Todays date. > > > > I tried modifying the WorkDate name WORKDAY(TODAY(),14) but it does not > > like > > when I use < sign in it. I also used =$AI$8<WorkDate but does not work > > out. > > > > Can you help me again - I so appreciate your help in me learning Excel > > -- > > Regards > > Mike > > > > > > "T. Valko" wrote: > > > >> >Would you be able to test it again for me just to make sure > >> > >> Worked ok for me. Here' what I did: > >> > >> Defined WorkDate as =WORKDAY(TODAY(),14) > >> > >> Applied the conditional formatting: > >> > >> Selected cell AI8 > >> Format>Conditional Formatting > >> Formula Is: =$AI$8>WorkDate > >> Set the fill color to green (any color will do, I just like green!) > >> > >> In AI8 I entered 12/31/2008 and it turned green as expected. > >> > >> =WORKDAY(TODAY(),14) evaluates to 7/21/2008 > >> > >> So, the format is applied when AI8 > 7/21/2008 (based on today's date). > >> > >> In AI8 I entered 7/20/2008 and the green format was removed. > >> > >> In AI8 I entered 7/25/2008 and the green format was again applied. > >> > >> > >> -- > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Mike" <(E-Mail Removed)> wrote in message > >> news CC03ED0-48E9-43BB-81AB-(E-Mail Removed)...> >> > Hi T, it seems so straight forward but I can't get this to work in the > >> > Conditional Formatting - highlight cell in red. I have tried testing it > >> > using > >> > a blank spreadsheet with a date >14 days old in cell A1 and created the > >> > Name > >> > as you suggested and retryed but no luck. No errors occur which is a > >> > bonus. > >> > > >> > Would you be able to test it again for me just to make sure > >> > > >> > Thanks again for all your help > >> > > >> > -- > >> > Regards > >> > Mike > >> > > >> > > >> > "T. Valko" wrote: > >> > > >> >> The WORKDAY function is part of the analysis ToolPak add-in which is > >> >> another > >> >> file. That's why you get the message. > >> >> > >> >> Try this: > >> >> > >> >> Create a defined named formula. > >> >> Goto Insert>Name>Define > >> >> Name: WorkDate > >> >> Refers to: =WORKDAY(TODAY(),14) > >> >> OK > >> >> > >> >> Then, use this formula in the conditional formatting: > >> >> > >> >> =$AI$8>WorkDate > >> >> > >> >> > >> >> -- > >> >> Biff > >> >> Microsoft Excel MVP > >> >> > >> >> > >> >> "Mike" <(E-Mail Removed)> wrote in message > >> >> news:F0F7247B-7D8E-47A9-B442-(E-Mail Removed)... > >> >> > Hi Bob, thanks for the quick reply back. However, I get a message as > >> >> > soon > >> >> > as > >> >> > I try to save it 'You may not use references from other worksheets > >> >> > for > >> >> > conditional fformatting criteria' > >> >> > > >> >> > Any clues on this? > >> >> > -- > >> >> > Regards > >> >> > Mike > >> >> > > >> >> > > >> >> > "Bob Phillips" wrote: > >> >> > > >> >> >> =$AI$8>WORKDAY(TODAY(),14) > >> >> >> > >> >> >> are you sure that you mean 14 working days, not 10? > >> >> >> > >> >> >> -- > >> >> >> HTH > >> >> >> > >> >> >> Bob > >> >> >> > >> >> >> (there's no email, no snail mail, but somewhere should be gmail in > >> >> >> my > >> >> >> addy) > >> >> >> > >> >> >> "Mike" <(E-Mail Removed)> wrote in message > >> >> >> news:B05052E1-BC5B-4DE7-91A0-(E-Mail Removed)... > >> >> >> > Hi all, hope you can help. Excel 2003 > >> >> >> > > >> >> >> > I need to use conditional formatting on a cell if the date in > >> >> >> > this > >> >> >> > cell > >> >> >> > is > >> >> >> > 14 working days > Today(). I'm not sure where to place the > >> >> >> > (AI1-Workday) > >> >> >> > part within the formula bar > >> >> >> > > >> >> >> > =TODAY()-$AI$8>14 > >> >> >> > -- > >> >> >> > Regards > >> >> >> > Mike > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Conditional Formatting of text effecting formatting of background | =?Utf-8?B?SEFI?= | Microsoft Access Reports | 6 | 25th Mar 2008 06:23 PM |
| Protect Cell Formatting including Conditional Formatting | =?Utf-8?B?TWljayBKZW5uaW5ncw==?= | Microsoft Excel Misc | 5 | 13th Nov 2007 05:32 PM |
| Conditional Formatting No Longer Conditional in 2007 Beta | =?Utf-8?B?Q2FjdHVhci1Oby1KdXRzdQ==?= | Microsoft Excel Crashes | 0 | 17th Nov 2006 10:01 PM |
| How do I do a complex conditional in a conditional formatting formula | Ray Stevens | Microsoft Excel Discussion | 7 | 12th Mar 2006 10:24 PM |
| Conditional Formatting that will display conditional data | =?Utf-8?B?QnJhaW5GYXJ0?= | Microsoft Excel Worksheet Functions | 1 | 13th Sep 2005 05:45 PM |
