Conditional Formatting: There's More!

[DOUG]

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[David Biddulph]

For a conditional formatting condition I would expect to see a formula that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to us
what you are trying to achieve with that part of the formula?

 

[DOUG]

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are over
60 days old, then the stoplight indicator in the A column will turn red. The
nuts and bolts of the expression are a little over my head, however. I did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

For a conditional formatting condition I would expect to see a formula that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[DOUG]

David: Also, the Conditional Formatting or Cell A151 (Stoplight Symbol) is
GREEN if "number" >30, Yellow if between 0 and 30 and Red if less than zero.
I am not sure how that relates to the dates in the rest of the columns in
that row.
But, if none of the training dates in the other columns are due or overdue,
the stoplight symbol should be green and it is not green in some cases.

DOUG

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are over
60 days old, then the stoplight indicator in the A column will turn red. The
nuts and bolts of the expression are a little over my head, however. I did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

For a conditional formatting condition I would expect to see a formula that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[David Biddulph]

It appears to me that you have been given gibberish.

The formula
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
is largely meaningless.
The first condition DATE(2010,4,16)<>"" is always TRUE
The second condition DATE(2010,4,16)>=DATE(7777,12,31) is always FALSE
So the formula could more sensible have been written not as
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
but as
=DATE(2010,4,16)

If this is typical of the material you have been given to work with, I
suggest that you throw it all away and start again from square one.
Perhaps the sheet was written as a joke?
--
David Biddulph

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are
over
60 days old, then the stoplight indicator in the A column will turn red.
The
nuts and bolts of the expression are a little over my head, however. I
did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should
have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

For a conditional formatting condition I would expect to see a formula
that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to
us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the
colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[DOUG]

David: Well, I am sure their intention was not humorous. But, you have a
good point. The "IF (Date..." expression throws me a bit. Which date do
they mean? Today's date? The date in some other cell? Then, it says "if
any of these things are true, then...[something?]". But, somehow, this is
generating a date in the subject cell.

In other words, this expression:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

shows "16-Apr-09". (So, I ask, why not just enter the date?).

The really vexing thing about this is the use of the stoplight symbol in
cell A1. It says that for all of the row, if "Number" >30, the circle is
green, if between 0 and 29 it is yellow and if it is less than zero, it is
red. This does not appear to correspond to the behavior in any of the other
date cells in that row. I believe it is trying to say that if any of the
training dates in the row are within 30 days of today (coming due) or less
than today (due), then display yellow or red as a warning. However, what the
"Number" means in this expression as it relates to dates is a mystery.

DOUG

It appears to me that you have been given gibberish.

The formula
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
is largely meaningless.
The first condition DATE(2010,4,16)<>"" is always TRUE
The second condition DATE(2010,4,16)>=DATE(7777,12,31) is always FALSE
So the formula could more sensible have been written not as
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
but as
=DATE(2010,4,16)

If this is typical of the material you have been given to work with, I
suggest that you throw it all away and start again from square one.
Perhaps the sheet was written as a joke?
--
David Biddulph

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are
over
60 days old, then the stoplight indicator in the A column will turn red.
The
nuts and bolts of the expression are a little over my head, however. I
did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should
have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

For a conditional formatting condition I would expect to see a formula
that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to
us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the
colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[Luke M]

The DATE functions has 3 arguements (year,month,day). As David said, it's
first asking if 16-Apr-2010 <> "" (ALWAYS TRUE!). Since the second IF
function is always first, it returns DATE(2010,4,16) aka 4/16/2010.

But to get back to your main topic. If conditional format is simply checking
if any of the days are within certain ranges, the following three formulas
will do.

first condition
formula is:
=MAX(M150:Y150)<TODAY()
Set formatting to display red icon
(If any of the dates in this range are before today's date, then red)

second condition
formula is:
=MAX(M150:Y150)<=TODAY()+30
Set formatting for yellow
(If any of the dates in this range are within 30 days of today, then yellow)

Make default formatting of cell to be green, or use third condition:
formula is:
=MAX(M150:Y150)>TODAY()+30

Hopefully this helps. As both David and I have pointed out, the formulas
you've been given are very poorly written, and it's probably better to start
over with the thought of "What do I want to happen?" as opposed to "What is
this trying to accomplish, and how do I make it work?"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

David: Well, I am sure their intention was not humorous. But, you have a
good point. The "IF (Date..." expression throws me a bit. Which date do
they mean? Today's date? The date in some other cell? Then, it says "if
any of these things are true, then...[something?]". But, somehow, this is
generating a date in the subject cell.

In other words, this expression:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

shows "16-Apr-09". (So, I ask, why not just enter the date?).

The really vexing thing about this is the use of the stoplight symbol in
cell A1. It says that for all of the row, if "Number" >30, the circle is
green, if between 0 and 29 it is yellow and if it is less than zero, it is
red. This does not appear to correspond to the behavior in any of the other
date cells in that row. I believe it is trying to say that if any of the
training dates in the row are within 30 days of today (coming due) or less
than today (due), then display yellow or red as a warning. However, what the
"Number" means in this expression as it relates to dates is a mystery.

DOUG

It appears to me that you have been given gibberish.

The formula
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
is largely meaningless.
The first condition DATE(2010,4,16)<>"" is always TRUE
The second condition DATE(2010,4,16)>=DATE(7777,12,31) is always FALSE
So the formula could more sensible have been written not as
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
but as
=DATE(2010,4,16)

If this is typical of the material you have been given to work with, I
suggest that you throw it all away and start again from square one.
Perhaps the sheet was written as a joke?
--
David Biddulph

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are
over
60 days old, then the stoplight indicator in the A column will turn red.
The
nuts and bolts of the expression are a little over my head, however. I
did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should
have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

:

For a conditional formatting condition I would expect to see a formula
that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to
us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the
colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[DOUG]

Luke: This is exactly what I am working on now. How do I join three
conditions together into one IF statement? Or, is this all to be placed in
Conditional Formatting instructions?

DOUG

The DATE functions has 3 arguements (year,month,day). As David said, it's
first asking if 16-Apr-2010 <> "" (ALWAYS TRUE!). Since the second IF
function is always first, it returns DATE(2010,4,16) aka 4/16/2010.

But to get back to your main topic. If conditional format is simply checking
if any of the days are within certain ranges, the following three formulas
will do.

first condition
formula is:
=MAX(M150:Y150)<TODAY()
Set formatting to display red icon
(If any of the dates in this range are before today's date, then red)

second condition
formula is:
=MAX(M150:Y150)<=TODAY()+30
Set formatting for yellow
(If any of the dates in this range are within 30 days of today, then yellow)

Make default formatting of cell to be green, or use third condition:
formula is:
=MAX(M150:Y150)>TODAY()+30

Hopefully this helps. As both David and I have pointed out, the formulas
you've been given are very poorly written, and it's probably better to start
over with the thought of "What do I want to happen?" as opposed to "What is
this trying to accomplish, and how do I make it work?"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

David: Well, I am sure their intention was not humorous. But, you have a
good point. The "IF (Date..." expression throws me a bit. Which date do
they mean? Today's date? The date in some other cell? Then, it says "if
any of these things are true, then...[something?]". But, somehow, this is
generating a date in the subject cell.

In other words, this expression:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

shows "16-Apr-09". (So, I ask, why not just enter the date?).

The really vexing thing about this is the use of the stoplight symbol in
cell A1. It says that for all of the row, if "Number" >30, the circle is
green, if between 0 and 29 it is yellow and if it is less than zero, it is
red. This does not appear to correspond to the behavior in any of the other
date cells in that row. I believe it is trying to say that if any of the
training dates in the row are within 30 days of today (coming due) or less
than today (due), then display yellow or red as a warning. However, what the
"Number" means in this expression as it relates to dates is a mystery.

DOUG

It appears to me that you have been given gibberish.

The formula
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
is largely meaningless.
The first condition DATE(2010,4,16)<>"" is always TRUE
The second condition DATE(2010,4,16)>=DATE(7777,12,31) is always FALSE
So the formula could more sensible have been written not as
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
but as
=DATE(2010,4,16)

If this is typical of the material you have been given to work with, I
suggest that you throw it all away and start again from square one.
Perhaps the sheet was written as a joke?
--
David Biddulph

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are
over
60 days old, then the stoplight indicator in the A column will turn red.
The
nuts and bolts of the expression are a little over my head, however. I
did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should
have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

:

For a conditional formatting condition I would expect to see a formula
that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to
us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the
colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[Luke M]

They should NOT be joined together. Within the Conditional Formatting
instructions, there should be a way to add additional conditions. Each of the
formulas I gave should be it's own condition, with a corresponding effect.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Luke: This is exactly what I am working on now. How do I join three
conditions together into one IF statement? Or, is this all to be placed in
Conditional Formatting instructions?

DOUG

The DATE functions has 3 arguements (year,month,day). As David said, it's
first asking if 16-Apr-2010 <> "" (ALWAYS TRUE!). Since the second IF
function is always first, it returns DATE(2010,4,16) aka 4/16/2010.

But to get back to your main topic. If conditional format is simply checking
if any of the days are within certain ranges, the following three formulas
will do.

first condition
formula is:
=MAX(M150:Y150)<TODAY()
Set formatting to display red icon
(If any of the dates in this range are before today's date, then red)

second condition
formula is:
=MAX(M150:Y150)<=TODAY()+30
Set formatting for yellow
(If any of the dates in this range are within 30 days of today, then yellow)

Make default formatting of cell to be green, or use third condition:
formula is:
=MAX(M150:Y150)>TODAY()+30

Hopefully this helps. As both David and I have pointed out, the formulas
you've been given are very poorly written, and it's probably better to start
over with the thought of "What do I want to happen?" as opposed to "What is
this trying to accomplish, and how do I make it work?"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

David: Well, I am sure their intention was not humorous. But, you have a
good point. The "IF (Date..." expression throws me a bit. Which date do
they mean? Today's date? The date in some other cell? Then, it says "if
any of these things are true, then...[something?]". But, somehow, this is
generating a date in the subject cell.

In other words, this expression:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

shows "16-Apr-09". (So, I ask, why not just enter the date?).

The really vexing thing about this is the use of the stoplight symbol in
cell A1. It says that for all of the row, if "Number" >30, the circle is
green, if between 0 and 29 it is yellow and if it is less than zero, it is
red. This does not appear to correspond to the behavior in any of the other
date cells in that row. I believe it is trying to say that if any of the
training dates in the row are within 30 days of today (coming due) or less
than today (due), then display yellow or red as a warning. However, what the
"Number" means in this expression as it relates to dates is a mystery.

DOUG

:

It appears to me that you have been given gibberish.

The formula
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
is largely meaningless.
The first condition DATE(2010,4,16)<>"" is always TRUE
The second condition DATE(2010,4,16)>=DATE(7777,12,31) is always FALSE
So the formula could more sensible have been written not as
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
but as
=DATE(2010,4,16)

If this is typical of the material you have been given to work with, I
suggest that you throw it all away and start again from square one.
Perhaps the sheet was written as a joke?
--
David Biddulph

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are
over
60 days old, then the stoplight indicator in the A column will turn red.
The
nuts and bolts of the expression are a little over my head, however. I
did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should
have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

:

For a conditional formatting condition I would expect to see a formula
that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to
us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the
colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT

 

[DOUG]

Luke: I typed
=IF(MAX(H4:Y4)<TODAY(),MAX(H4:Y4)<=TODAY()+30,MAX(H4:Y4)>TODAY()+30) which
returned "TRUE". But, when I converted it to an icon set (stoplights), the
cell went blank. Please advise.

Incidentally, I believe the reason for the poorly written instructions was
that only certain cells were to be included in the count, based upon job
classifications shown in other cells. It gets very complicated.

DOUG

Luke: This is exactly what I am working on now. How do I join three
conditions together into one IF statement? Or, is this all to be placed in
Conditional Formatting instructions?

DOUG

The DATE functions has 3 arguements (year,month,day). As David said, it's
first asking if 16-Apr-2010 <> "" (ALWAYS TRUE!). Since the second IF
function is always first, it returns DATE(2010,4,16) aka 4/16/2010.

But to get back to your main topic. If conditional format is simply checking
if any of the days are within certain ranges, the following three formulas
will do.

first condition
formula is:
=MAX(M150:Y150)<TODAY()
Set formatting to display red icon
(If any of the dates in this range are before today's date, then red)

second condition
formula is:
=MAX(M150:Y150)<=TODAY()+30
Set formatting for yellow
(If any of the dates in this range are within 30 days of today, then yellow)

Make default formatting of cell to be green, or use third condition:
formula is:
=MAX(M150:Y150)>TODAY()+30

Hopefully this helps. As both David and I have pointed out, the formulas
you've been given are very poorly written, and it's probably better to start
over with the thought of "What do I want to happen?" as opposed to "What is
this trying to accomplish, and how do I make it work?"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

David: Well, I am sure their intention was not humorous. But, you have a
good point. The "IF (Date..." expression throws me a bit. Which date do
they mean? Today's date? The date in some other cell? Then, it says "if
any of these things are true, then...[something?]". But, somehow, this is
generating a date in the subject cell.

In other words, this expression:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

shows "16-Apr-09". (So, I ask, why not just enter the date?).

The really vexing thing about this is the use of the stoplight symbol in
cell A1. It says that for all of the row, if "Number" >30, the circle is
green, if between 0 and 29 it is yellow and if it is less than zero, it is
red. This does not appear to correspond to the behavior in any of the other
date cells in that row. I believe it is trying to say that if any of the
training dates in the row are within 30 days of today (coming due) or less
than today (due), then display yellow or red as a warning. However, what the
"Number" means in this expression as it relates to dates is a mystery.

DOUG

:

It appears to me that you have been given gibberish.

The formula
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
is largely meaningless.
The first condition DATE(2010,4,16)<>"" is always TRUE
The second condition DATE(2010,4,16)>=DATE(7777,12,31) is always FALSE
So the formula could more sensible have been written not as
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))
but as
=DATE(2010,4,16)

If this is typical of the material you have been given to work with, I
suggest that you throw it all away and start again from square one.
Perhaps the sheet was written as a joke?
--
David Biddulph

David: I did not write it, but have been asked to help repair it. It
appears to me to say that if any of the dates in the various columns are
over
60 days old, then the stoplight indicator in the A column will turn red.
The
nuts and bolts of the expression are a little over my head, however. I
did
point out to the user that all but one of the date cells were based upon
formulas, i.e., someone had entered a plain date in a cell that should
have
displayed a formula. That cell should display the following formula:
=IF(DATE(2010,4,16)<>"",IF(DATE(2010,4,16)>=DATE(7777,12,31),"One Time
Only",(DATE(2010,4,16))))

and I do not know what this means either.

DOUG

:

For a conditional formatting condition I would expect to see a formula
that
returns TRUE or FALSE, so I'm not sure why you have a formula which is
returning numbers like -60.
-60 (or any number other than zero) will be treated as TRUE, but it isn't
clear why you are using an IF statement of that form.
Perhaps you could explain what you are trying to do?

The alternative to -60 is the expression
IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())
and here your value_if_true and value_if_false are identical
[MIN(H150:J150,M150:Y150)-TODAY()], so again perhaps you could explain to
us
what you are trying to achieve with that part of the formula?
--
David Biddulph

Please evaluate this:
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

This is supposed to make the stoplight symbol in column A match the
colors
in subsequent cells in the same row, but it does not work.

DOUG ECKERT