Conditional formula date
 

On Tue, 25 Oct 2005 05:07:02 -0500, tghcogo
wrote:


Hi there I want to apply a conditional format to a cell to see if a date
is present.

eg if there is a comment like "SEE JUNE" it becomes red text, otherwise
the date format in the cell remains as it

I'm sure there is a much simpler way to do it than what I am currently
attempting.

TGHC

It seems your definition of a "date" may not be the same as Excel's definition.

Excel stores dates as serial numbers beginning with 1/1/1900 (or 1/1/1904) and
can display them in various formats.

If you want text RED if a comment 'like' "SEE JUNE" is present, you will have
to PRECISELY define what you mean by 'like'.

If you define it as the name of a month being present, in TEXT format without a
date, AND always the last word in the phrase, you could use these 2 CF
functions:

Condition 1
Formula Is:
=NOT(ISERROR(DATEVALUE("1 "&MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))))

Condition 2
Formula Is:
=NOT(ISERROR(DATEVALUE("1 "&A1)))

You can't combine these two formulas with a single OR statement as you run into
the seven function nesting limit of Excel.

If your phrase will always be at least two words, then the second formula is
not required.

Note that misspellings of the month will not be interpreted as a month.

If your possibilities are more complex, then so will be the solution.


--ron

Conditional formula date
 

On Tue, 25 Oct 2005 08:56:23 -0400, Ron Rosenfeld
wrote:

On Tue, 25 Oct 2005 05:07:02 -0500, tghcogo
wrote:


Hi there I want to apply a conditional format to a cell to see if a date
is present.

eg if there is a comment like "SEE JUNE" it becomes red text, otherwise
the date format in the cell remains as it

I'm sure there is a much simpler way to do it than what I am currently
attempting.

TGHC

It seems your definition of a "date" may not be the same as Excel's definition.

Excel stores dates as serial numbers beginning with 1/1/1900 (or 1/1/1904) and
can display them in various formats.

If you want text RED if a comment 'like' "SEE JUNE" is present, you will have
to PRECISELY define what you mean by 'like'.

If you define it as the name of a month being present, in TEXT format without a
date, AND always the last word in the phrase, you could use these 2 CF
functions:

Condition 1
Formula Is:
=NOT(ISERROR(DATEVALUE("1 "&MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))))

Condition 2
Formula Is:
=NOT(ISERROR(DATEVALUE("1 "&A1)))

You can't combine these two formulas with a single OR statement as you run into
the seven function nesting limit of Excel.

If your phrase will always be at least two words, then the second formula is
not required.

Note that misspellings of the month will not be interpreted as a month.

If your possibilities are more complex, then so will be the solution.


--ron


Correction:

Those formulas will pick up a month name anyplace in the string; it does not
have to be last.


--ron