If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 news 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 |
#7
|
|||
|
|||
Conditional Formatting question
Yes, select all the target cells, and when applying CF, make sure you refer
to the first of the selected cells. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Helen Holubowicz" wrote in message ... 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 news 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 |
#8
|
|||
|
|||
Conditional Formatting question
FIRE TRAINING FOOD + HYGIENE CPR
DURATION 2 1 1 helen 10/12/2006 01/28/06 12/12/06 date Due December 10, 2008 January 28, 2007 December 12, 2007 Piers 30/12/2005 10/10/2004 01/01/2006 Date Due December 30, 2007 October 10, 2005 January 1, 2007 Hi Please can you clarify what you mean by my above sample of my spreadsheet, It is very difficult to explain things sometimes , I would like to apply CF to the whole ws, so that if the training Date Due is 31 days to go, or 15, or overdue it will change font colour. Thanks "Helen Holubowicz" wrote in message ... 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 news 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 |
#9
|
|||
|
|||
Conditional Formatting question
Right,
I think i have the hang of it now. Thanks very much for your help "Helen Holubowicz" wrote in message ... FIRE TRAINING FOOD + HYGIENE CPR DURATION 2 1 1 helen 10/12/2006 01/28/06 12/12/06 date Due December 10, 2008 January 28, 2007 December 12, 2007 Piers 30/12/2005 10/10/2004 01/01/2006 Date Due December 30, 2007 October 10, 2005 January 1, 2007 Hi Please can you clarify what you mean by my above sample of my spreadsheet, It is very difficult to explain things sometimes , I would like to apply CF to the whole ws, so that if the training Date Due is 31 days to go, or 15, or overdue it will change font colour. Thanks "Helen Holubowicz" wrote in message ... 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 news 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 |
Thread Tools | |
Display Modes | |
|
|