Conditional formula date

T

tghcogo

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
 
T

tghcogo

Sorry just a ruse to get some response, I'm a desperate man lol. I'
sure there is a really simple solution to this.

thanks
 
W

William Horton

You can use the following conditional format. Highlite the cell where you
want to put the conditional format. Follow the menu path Format /
Conditional Formatting. Under condition 1 do the drop down menu and choose
"Formula Is". Then put = NOT(ISNUMBER(A1)) in the proper section. Then
click on the format button and make the format for what you want to happen if
the value is not a date (1/1/05). Click OK when done. This should work.
You can use the format painter to copy the conditional formatting to other
cells.

Hope this helps.

Bill Horton
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...


Correction:

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


--ron
 
Top