Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting query
Hi all,
I need to place conditions on a spreadsheet dealing with dates. In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc Lets say they are in cells B3, B4 and B5 respectively. At cell A1 there is a date =TODAY() In the B column I have placed conditional formatting as Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured RED I'm at a loss to place a value in for those dates between Cell A and those in Column B from day 1 to day 90 (ie within 3 months) to return a colour of Yellow Hope you can understand my query, Cheers, Pete. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting query
Select the range to be conditionally formatted,
eg select B3:B5 (with B3 active), then apply CF using "Formula is" as follows: Condition 1, Formula is: =AND(B3<"",B3-$A$1<=0) Format Red fill Condition 2, Formula is: =AND(B3<"",B3-$A$10,B3-$A$1<=90) Format Yellow fill -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Potaroo" wrote in message ... Hi all, I need to place conditions on a spreadsheet dealing with dates. In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc Lets say they are in cells B3, B4 and B5 respectively. At cell A1 there is a date =TODAY() In the B column I have placed conditional formatting as Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured RED I'm at a loss to place a value in for those dates between Cell A and those in Column B from day 1 to day 90 (ie within 3 months) to return a colour of Yellow Hope you can understand my query, Cheers, Pete. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting query
well, guess you could/should always close off the traffic lights with a
condition 3 for green? Condition 3, Formula is: =AND(B3<"",B3-$A$190) Format Green fill The check: B3<"" ensures that blank cells will not trigger the CF -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting query
Oh Max, it didn't work ;-(
Checked, made sure that the cells were formatted the same, ie "Number" "Date" 08-Jun-07" etc, but to no avail. "Max" wrote in message ... Select the range to be conditionally formatted, eg select B3:B5 (with B3 active), then apply CF using "Formula is" as follows: Condition 1, Formula is: =AND(B3<"",B3-$A$1<=0) Format Red fill Condition 2, Formula is: =AND(B3<"",B3-$A$10,B3-$A$1<=90) Format Yellow fill -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Potaroo" wrote in message ... Hi all, I need to place conditions on a spreadsheet dealing with dates. In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc Lets say they are in cells B3, B4 and B5 respectively. At cell A1 there is a date =TODAY() In the B column I have placed conditional formatting as Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured RED I'm at a loss to place a value in for those dates between Cell A and those in Column B from day 1 to day 90 (ie within 3 months) to return a colour of Yellow Hope you can understand my query, Cheers, Pete. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting query
No reason why it shouldn't work for you unless the source dates in B3:B5 are
not real dates recognized by Excel. Note that formatting in itself doesn't convert text dates to real dates. You can use Data Text to Columns to convert. Select the range of dates, eg B3:B5, then click Data Text to Columns. Click Next Next. In step 3 of the wiz., check Date under "Column data format", then select the appropriate date format from the droplist, eg: DMY, and click Finish. Here's a sample which illustrates the CF set-up (working, of course): http://www.savefile.com/files/723204 CF_Dates_Traffic Lights.xls Let me know here how it went for you .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Potaroo" wrote in message ... Oh Max, it didn't work ;-( Checked, made sure that the cells were formatted the same, ie "Number" "Date" 08-Jun-07" etc, but to no avail. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting query
Hi
You say the cells are formatted as Date, but was this before or after data was entered? What do you get if you enter a formula on the sheet =B3+1? Max's formulae should work perfectly, what result are you seeing? -- Regards Roger Govier "Potaroo" wrote in message ... Oh Max, it didn't work ;-( Checked, made sure that the cells were formatted the same, ie "Number" "Date" 08-Jun-07" etc, but to no avail. "Max" wrote in message ... Select the range to be conditionally formatted, eg select B3:B5 (with B3 active), then apply CF using "Formula is" as follows: Condition 1, Formula is: =AND(B3<"",B3-$A$1<=0) Format Red fill Condition 2, Formula is: =AND(B3<"",B3-$A$10,B3-$A$1<=90) Format Yellow fill -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Potaroo" wrote in message ... Hi all, I need to place conditions on a spreadsheet dealing with dates. In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc Lets say they are in cells B3, B4 and B5 respectively. At cell A1 there is a date =TODAY() In the B column I have placed conditional formatting as Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured RED I'm at a loss to place a value in for those dates between Cell A and those in Column B from day 1 to day 90 (ie within 3 months) to return a colour of Yellow Hope you can understand my query, Cheers, Pete. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional sum query | Excel Worksheet Functions | |||
Conditional formatting query | Excel Worksheet Functions | |||
Conditional Query | Excel Discussion (Misc queries) | |||
Conditional MS Query | Excel Worksheet Functions | |||
How to preserve conditional formatting on a web query table result | Excel Discussion (Misc queries) |