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

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

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

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

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

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

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

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

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

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

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