Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting question
Hi I have a list of dates that will be entered, i have already a formula
that will calculate say 2 years on from the date, but i need the conditional formatting to change the font to show me the dates from a month in advance, In the dialog box i have tried 1. =today 2. between =today and =today+31 but im getting intermittent results, can anyone help? Ta |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting question
Try a formula of
=AND(A2=TODAY()-31,A2<=TODAY()) where A2 is the cell in question -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Hi I have a list of dates that will be entered, i have already a formula that will calculate say 2 years on from the date, but i need the conditional formatting to change the font to show me the dates from a month in advance, In the dialog box i have tried 1. =today 2. between =today and =today+31 but im getting intermittent results, can anyone help? Ta |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting question
Bob, that didn't seem to work, though i tried applying it to the whole sheet
this is my layout: Duration [a2] 2 [b2] Helen [a3] 01/28/06 [b3] Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4] There are multiple names and dates. maybe there is a shorter/easier formula to use too ? Thanks =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) "Bob Phillips" wrote in message ... Try a formula of =AND(A2=TODAY()-31,A2<=TODAY()) where A2 is the cell in question -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Hi I have a list of dates that will be entered, i have already a formula that will calculate say 2 years on from the date, but i need the conditional formatting to change the font to show me the dates from a month in advance, In the dialog box i have tried 1. =today 2. between =today and =today+31 but im getting intermittent results, can anyone help? Ta |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting question
What cell are you trying to format?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Bob, that didn't seem to work, though i tried applying it to the whole sheet this is my layout: Duration [a2] 2 [b2] Helen [a3] 01/28/06 [b3] Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4] There are multiple names and dates. maybe there is a shorter/easier formula to use too ? Thanks =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) "Bob Phillips" wrote in message ... Try a formula of =AND(A2=TODAY()-31,A2<=TODAY()) where A2 is the cell in question -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Hi I have a list of dates that will be entered, i have already a formula that will calculate say 2 years on from the date, but i need the conditional formatting to change the font to show me the dates from a month in advance, In the dialog box i have tried 1. =today 2. between =today and =today+31 but im getting intermittent results, can anyone help? Ta |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting question
Hi Bob and Hellen!
to test what I was doing, i created this data in a clean worksheet: b1 2/2/2007 b2 2 b3 1/5/2005 b4 1/5/2007 1/2/2007 3 in formula view: b1 =TODAY()+31 b2 2 b3 38357 b4/c4/d4 =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) =TODAY() =B4-C4 I then added three conditional formats to B4: Conditional Formats for "b4" Cell is Yellow (Due within 31 days) Formula is =AND(B4<=TODAY()+31,B4=TODAY()+15) Cell is Orange (Due within 15 days) Formula is =AND(B4<=TODAY()+15,B4=TODAY()) Cell is Red (overdue) Cell Value Is less than =TODAY() This seems to work, and it gives you a 15 day warning and a 31 day warning, as well as an overdue warning. Is that what you were looking for? Please let us know if any of this has helped. Thanks Doug "Bob Phillips" wrote: What cell are you trying to format? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Bob, that didn't seem to work, though i tried applying it to the whole sheet this is my layout: Duration [a2] 2 [b2] Helen [a3] 01/28/06 [b3] Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4] There are multiple names and dates. maybe there is a shorter/easier formula to use too ? Thanks =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) "Bob Phillips" wrote in message ... Try a formula of =AND(A2=TODAY()-31,A2<=TODAY()) where A2 is the cell in question -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Hi I have a list of dates that will be entered, i have already a formula that will calculate say 2 years on from the date, but i need the conditional formatting to change the font to show me the dates from a month in advance, In the dialog box i have tried 1. =today 2. between =today and =today+31 but im getting intermittent results, can anyone help? Ta |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting question
Hi Songbear
Thanks as soon as i get a chance Ill try this but in the meantime, can I apply the conditional formatting to the whole worksheet, and not just a single cell, in case a a date is calculated.. and it is in the range... Thanks "SongBear" wrote in message ... Hi Bob and Hellen! to test what I was doing, i created this data in a clean worksheet: b1 2/2/2007 b2 2 b3 1/5/2005 b4 1/5/2007 1/2/2007 3 in formula view: b1 =TODAY()+31 b2 2 b3 38357 b4/c4/d4 =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) =TODAY() =B4-C4 I then added three conditional formats to B4: Conditional Formats for "b4" Cell is Yellow (Due within 31 days) Formula is =AND(B4<=TODAY()+31,B4=TODAY()+15) Cell is Orange (Due within 15 days) Formula is =AND(B4<=TODAY()+15,B4=TODAY()) Cell is Red (overdue) Cell Value Is less than =TODAY() This seems to work, and it gives you a 15 day warning and a 31 day warning, as well as an overdue warning. Is that what you were looking for? Please let us know if any of this has helped. Thanks Doug "Bob Phillips" wrote: What cell are you trying to format? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Bob, that didn't seem to work, though i tried applying it to the whole sheet this is my layout: Duration [a2] 2 [b2] Helen [a3] 01/28/06 [b3] Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4] There are multiple names and dates. maybe there is a shorter/easier formula to use too ? Thanks =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) "Bob Phillips" wrote in message ... Try a formula of =AND(A2=TODAY()-31,A2<=TODAY()) where A2 is the cell in question -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... Hi I have a list of dates that will be entered, i have already a formula that will calculate say 2 years on from the date, but i need the conditional formatting to change the font to show me the dates from a month in advance, In the dialog box i have tried 1. =today 2. between =today and =today+31 but im getting intermittent results, can anyone help? Ta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Question | Excel Worksheet Functions | |||
Tough conditional formatting question | Excel Discussion (Misc queries) | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |