|
Conditional Formatting
I want to conditional Format Rows depending on there being a date entered in
Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting - Additional Question to my first posting..
Can the conditional Formatting of the ROW Range be done by viewing the date entry in Cell $R6 as Text as compared to a Date format? "Dermot" wrote: I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Dermot
Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Roger,
Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Roger,
I also want to conditional format the cells in a column column for date after 2001. I highlighted the whole column, then Format | Conditional format and entered the same formula =$Y2DATE(2001,1,1) and chose blue.....so when a date is entered in any cell in column B, the cell is formatted as blue....the problem is it formats the cell above the cell the date is entered....i.e. enter date in A3, and the formatting is wrongly applied to cell A".what am I overlooking. Thanks again. "Dermot" wrote: Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Dermot
Date has 3 parameters =Date(Year,Month,Day) If you want to combine to and from dates then =AND($R6=DATE(2008,1,1),$R6<=DATE(2008,1,31)) would include all dates from 1st to 31st January 2008 -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Thanks Roger
Regards Dermot "Roger Govier" wrote: Hi Dermot Date has 3 parameters =Date(Year,Month,Day) If you want to combine to and from dates then =AND($R6=DATE(2008,1,1),$R6<=DATE(2008,1,31)) would include all dates from 1st to 31st January 2008 -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Roger,
Another problem has shown fo me, please advise. I am now using three conditiona formats: Across the Row Range A200:Z200 1.=$X6DATE(2001,1,1).............Format Orange 2.=$Z6DATE(2001,1,1).............Format Pink 3.=$Y6DATE(2001,1,1).............Format Green 1. Works when I enter a date in any cell in Column X....ROW goes Orange 2. Works when I enter date in cell Column z....Row Goes Pink 3. I cannot get .=$Y6DATE(2001,1,1).............Format Green to work. I think this is because I have already conditionally formatted and when I enter a date in an appropriate Pink or Orange Row, it does not return the row to cleared, or Green. I assume I have to create a formula to override the relevant previous formatting of the ROW range, and then excecute .=$Y6DATE(2001,1,1)., changing the Orange or pink row to Green. Please advise what I may be overlooking. Any suggested formulae would be appreciated Thanks Dermot "Dermot" wrote: Thanks Roger Regards Dermot "Roger Govier" wrote: Hi Dermot Date has 3 parameters =Date(Year,Month,Day) If you want to combine to and from dates then =AND($R6=DATE(2008,1,1),$R6<=DATE(2008,1,31)) would include all dates from 1st to 31st January 2008 -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Dermot
Depending upon what you are wanting, you need to AND some conditions together. =AND($X6DATE(2000,1,1),$Z6<DATE(2000,1,1),$Y$6<DA TE(2000,1,1)) format Orange =AND($X6DATE(2000,1,1),$Z6DATE(2000,1,1),$Y$6<DA TE(2000,1,1)) format Pink =AND($X6DATE(2000,1,1),$Z6DATE(2000,1,1),$Y$6DA TE(2000,1,1)) format Green This is saying If X6 is greater than the date, and Z6 and Y6 are not, then format Green If both X6 and Z6 are greater than the date, but Y6 is not, then format Pink If all 3 are greater than the date then format Green. If any other combination exists, don't apply any colour at all. Depending on what you are wanting to show, play about with those conditions. There is also an OR function that can be used. Take a look at Help on AND and OR. You can combine AND and OR in a single function. -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Another problem has shown fo me, please advise. I am now using three conditiona formats: Across the Row Range A200:Z200 1.=$X6DATE(2001,1,1).............Format Orange 2.=$Z6DATE(2001,1,1).............Format Pink 3.=$Y6DATE(2001,1,1).............Format Green 1. Works when I enter a date in any cell in Column X....ROW goes Orange 2. Works when I enter date in cell Column z....Row Goes Pink 3. I cannot get .=$Y6DATE(2001,1,1).............Format Green to work. I think this is because I have already conditionally formatted and when I enter a date in an appropriate Pink or Orange Row, it does not return the row to cleared, or Green. I assume I have to create a formula to override the relevant previous formatting of the ROW range, and then excecute .=$Y6DATE(2001,1,1)., changing the Orange or pink row to Green. Please advise what I may be overlooking. Any suggested formulae would be appreciated Thanks Dermot "Dermot" wrote: Thanks Roger Regards Dermot "Roger Govier" wrote: Hi Dermot Date has 3 parameters =Date(Year,Month,Day) If you want to combine to and from dates then =AND($R6=DATE(2008,1,1),$R6<=DATE(2008,1,31)) would include all dates from 1st to 31st January 2008 -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Roger
Thanks for the reply and example formulae for me to investigate. I was looking at the AND and OR functions last night on both Help and other internet sites afer posting. I wasn't having much success .......I didn't quite understand how many fucntions I could incorporate and although I was following syntax and parenthsis , I kept getting errors. I think the difficult part is working out the knock on effect of multiple changes...I think possible I was conflicting formats if you know what I mean.....if that makes sense......not sure about this mmmm. I will work with these excellent example with the aim of gaining a better understanding to achive the outcome I am seeking. Cheers Dermot "Roger Govier" wrote: Hi Dermot Depending upon what you are wanting, you need to AND some conditions together. =AND($X6DATE(2000,1,1),$Z6<DATE(2000,1,1),$Y$6<DA TE(2000,1,1)) format Orange =AND($X6DATE(2000,1,1),$Z6DATE(2000,1,1),$Y$6<DA TE(2000,1,1)) format Pink =AND($X6DATE(2000,1,1),$Z6DATE(2000,1,1),$Y$6DA TE(2000,1,1)) format Green This is saying If X6 is greater than the date, and Z6 and Y6 are not, then format Green If both X6 and Z6 are greater than the date, but Y6 is not, then format Pink If all 3 are greater than the date then format Green. If any other combination exists, don't apply any colour at all. Depending on what you are wanting to show, play about with those conditions. There is also an OR function that can be used. Take a look at Help on AND and OR. You can combine AND and OR in a single function. -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Another problem has shown fo me, please advise. I am now using three conditiona formats: Across the Row Range A200:Z200 1.=$X6DATE(2001,1,1).............Format Orange 2.=$Z6DATE(2001,1,1).............Format Pink 3.=$Y6DATE(2001,1,1).............Format Green 1. Works when I enter a date in any cell in Column X....ROW goes Orange 2. Works when I enter date in cell Column z....Row Goes Pink 3. I cannot get .=$Y6DATE(2001,1,1).............Format Green to work. I think this is because I have already conditionally formatted and when I enter a date in an appropriate Pink or Orange Row, it does not return the row to cleared, or Green. I assume I have to create a formula to override the relevant previous formatting of the ROW range, and then excecute .=$Y6DATE(2001,1,1)., changing the Orange or pink row to Green. Please advise what I may be overlooking. Any suggested formulae would be appreciated Thanks Dermot "Dermot" wrote: Thanks Roger Regards Dermot "Roger Govier" wrote: Hi Dermot Date has 3 parameters =Date(Year,Month,Day) If you want to combine to and from dates then =AND($R6=DATE(2008,1,1),$R6<=DATE(2008,1,31)) would include all dates from 1st to 31st January 2008 -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
Conditional Formatting
Hi Roger
I was going to enter this as a fresh posting, but I think it's relevant, and continuity, save a full explanation. I am further forward with the conditional formatting now, but now find I have some formatting I want to delete. I have some random ranges on my work sheet ( from trial and error) where when I enter a date, I get formatting I do not want....Going to Edit | Goto....Special and selecting " Conditional Formatting", does not seem to help locate where I have entered it. ......I have deleted several entries but can't find it's source. Is there a way to locate the range and relevant formula entered to cause the formatting? Thanks in advance. "Dermot" wrote: Hi Roger Thanks for the reply and example formulae for me to investigate. I was looking at the AND and OR functions last night on both Help and other internet sites afer posting. I wasn't having much success .......I didn't quite understand how many fucntions I could incorporate and although I was following syntax and parenthsis , I kept getting errors. I think the difficult part is working out the knock on effect of multiple changes...I think possible I was conflicting formats if you know what I mean.....if that makes sense......not sure about this mmmm. I will work with these excellent example with the aim of gaining a better understanding to achive the outcome I am seeking. Cheers Dermot "Roger Govier" wrote: Hi Dermot Depending upon what you are wanting, you need to AND some conditions together. =AND($X6DATE(2000,1,1),$Z6<DATE(2000,1,1),$Y$6<DA TE(2000,1,1)) format Orange =AND($X6DATE(2000,1,1),$Z6DATE(2000,1,1),$Y$6<DA TE(2000,1,1)) format Pink =AND($X6DATE(2000,1,1),$Z6DATE(2000,1,1),$Y$6DA TE(2000,1,1)) format Green This is saying If X6 is greater than the date, and Z6 and Y6 are not, then format Green If both X6 and Z6 are greater than the date, but Y6 is not, then format Pink If all 3 are greater than the date then format Green. If any other combination exists, don't apply any colour at all. Depending on what you are wanting to show, play about with those conditions. There is also an OR function that can be used. Take a look at Help on AND and OR. You can combine AND and OR in a single function. -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Another problem has shown fo me, please advise. I am now using three conditiona formats: Across the Row Range A200:Z200 1.=$X6DATE(2001,1,1).............Format Orange 2.=$Z6DATE(2001,1,1).............Format Pink 3.=$Y6DATE(2001,1,1).............Format Green 1. Works when I enter a date in any cell in Column X....ROW goes Orange 2. Works when I enter date in cell Column z....Row Goes Pink 3. I cannot get .=$Y6DATE(2001,1,1).............Format Green to work. I think this is because I have already conditionally formatted and when I enter a date in an appropriate Pink or Orange Row, it does not return the row to cleared, or Green. I assume I have to create a formula to override the relevant previous formatting of the ROW range, and then excecute .=$Y6DATE(2001,1,1)., changing the Orange or pink row to Green. Please advise what I may be overlooking. Any suggested formulae would be appreciated Thanks Dermot "Dermot" wrote: Thanks Roger Regards Dermot "Roger Govier" wrote: Hi Dermot Date has 3 parameters =Date(Year,Month,Day) If you want to combine to and from dates then =AND($R6=DATE(2008,1,1),$R6<=DATE(2008,1,31)) would include all dates from 1st to 31st January 2008 -- Regards Roger Govier "Dermot" wrote in message ... Hi Roger, Thanks for your formulae, they worked fine. Please advise a little further.......I spent quite a while trying to figure out the logic of he expression I was trying to determine......I knew I was complicating matters! =$R6--("1/1/2001") From left to right the above formula read Cell $R6 is greater than 1/1/2002..................can I put from and to dates in the brackets, if so what would the syntax be......or how would I adapt the formula. or =$R6DATE(2001,1,1) Reads..........$R6 is greater than.......how do I interpret the contents of the brackets?....Year, Month, day....the 1,1 representing month and day? -- Thanks Dermot "Roger Govier" wrote: Hi Dermot Try =$R6--("1/1/2001") or =$R6DATE(2001,1,1) -- Regards Roger Govier "Dermot" wrote in message ... I want to conditional Format Rows depending on there being a date entered in Cell $R6, so the Row goes Yellow. If there are no dates within column R6, I want the Cell to remain blank I have tried using several formula, to no success, the las being, =IF(DATE(1/1/2001),1,"") .............for condition 1 Assistance would be appreciated how I can achieve this. Thanks in advance |
| All times are GMT +1. The time now is 07:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com