Conditional Formatting

[Dermot]

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

 

[Dermot]

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?

 

[Roger Govier]

Hi Dermot
Try
=$R6>--("1/1/2001")
or
=$R6>DATE(2001,1,1)

 

[Dermot]

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
=$R6>DATE(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?

 

[Dermot]

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 =$Y2>DATE(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.

 

[Roger Govier]

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

 

[Dermot]

Thanks Roger
Regards
Dermot

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

 

[Dermot]

Hi Roger,
Another problem has shown fo me, please advise.

I am now using three conditiona formats: Across the Row Range A200:Z200
1.=$X6>DATE(2001,1,1).............Format Orange
2.=$Z6>DATE(2001,1,1).............Format Pink
3.=$Y6>DATE(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 .=$Y6>DATE(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 .=$Y6>DATE(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

 

[Roger Govier]

Hi Dermot
Depending upon what you are wanting, you need to AND some conditions
together.
=AND($X6>DATE(2000,1,1),$Z6<DATE(2000,1,1),$Y$6<DATE(2000,1,1)) format
Orange
=AND($X6>DATE(2000,1,1),$Z6>DATE(2000,1,1),$Y$6<DATE(2000,1,1)) format Pink
=AND($X6>DATE(2000,1,1),$Z6>DATE(2000,1,1),$Y$6>DATE(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.

 

[Dermot]

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

 

[Dermot]

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.