Conditional formatting: How to set condition "formula" with is "date"formatted

[AA Arens]

I want to use Conditional Formatting. I opt for "formula". The
condition should be that a cell F9 that is formatted to Date 14-Mar-07
(see cell format) need to be filled with a date (any date). If empty
then FALSE.

What is the formulah I need to fill in. DATE(F9<>0) failed for me.

This is the condition that is set for cell H9.

Thank you.

Bart
Excel 2003

 

[Bob Phillips]

=AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AA Arens]

=AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi, this solution did not work for me.


May be I was not so clear and I realised there is another condition.
This is what I would like:

Column:
F G H --> Z
9 (Date) (amount)
10 (Date) (amount)
etc. etc.

Dual conditional formatting:

Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
is filled with amount
Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
is filled with amount AND the date column is filled with a date
In other cases cell does not get fill color.

Bart

 

[Bob Phillips]

Hi, this solution did not work for me.


May be I was not so clear and I realised there is another condition.
This is what I would like:

Column:
F G H --> Z
9 (Date) (amount)
10 (Date) (amount)
etc. etc.

Dual conditional formatting:

Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
is filled with amount
Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
is filled with amount AND the date column is filled with a date
In other cases cell does not get fill color.

Bart

Condition 2: =ISNUMBER(G9)

Condition 1:
=AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

 

[AA Arens]

Condition 2: =ISNUMBER(G9)

Condition 1:
=AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

Bob, I can't get it working. I filled both conditions in cell G9,
first condition 1 then 2. But only the format I have assigned to
condition 2 is visible if G9 has data, whether F9 is filled in or not.

Bart

 

[Bob Phillips]

It might be because I didn't fix F9 down using absolute referencing.

I have posted an example at http://cjoint.com/?bDnSVGsfR1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AA Arens]

It might be because I didn't fix F9 down using absolute referencing.

I have posted an example athttp://cjoint.com/?bDnSVGsfR1

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob, it works fine, triggy idea. I also hided the second date
column. As the cells right of the date need to chege colors, I changed
$F$9 into $F9.

Just a small add. I also have a combo column left of the date column
where I can choose the currency either USD, EUR, or IDR. How to have
the cells that are filled in the G-Z area also have italics in case I
choose EUR. In fact there should be a third condition beside the one
we discussed before. What is this condition?




Bart

 

[Bob Phillips]

Bart,

Is that 4 conditions?

Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is
italicised if any cell is filled with amount AND currency is EUR
Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is
italicised if any cell is filled with amount AND the date column is filled
with a date AND currency is EUR

Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is not
italicised if any cell is filled with amount AND currency is not EUR
Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is not
italicised if any cell is filled with amount AND the date column is filled
with a date AND currency is not EUR
In other cases cell does not get fill colour.

If so, that becomes tricky as CF only as 3 conditions.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AA Arens]

Bart,

Is that 4 conditions?

Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is
italicised if any cell is filled with amount AND currency is EUR
Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is
italicised if any cell is filled with amount AND the date column is filled
with a date AND currency is EUR

Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is not
italicised if any cell is filled with amount AND currency is not EUR
Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is not
italicised if any cell is filled with amount AND the date column is filled
with a date AND currency is not EUR
In other cases cell does not get fill colour.

If so, that becomes tricky as CF only as 3 conditions.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hmm, if an additional format of a cell value should be italics when a
combo value from another column is set to EUR, I think its one extra
condition because the extra format applies independently of the
"orange -" or "green condition".

Bart

 

[Bob Phillips]

Okay, what column is the currency in?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AA Arens]

Okay, what column is the currency in?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Goodmorning.

The currency combo box column is located left of the date column.

So I have: Currency (D) - Date (E) - Date (hided column, for
triggering) (F) - G/Z are columns with invoice value

G/Z cells are either blank (empty) or, if not empty:

- they are green/orange (depend if date cell is filled with date) ->
Works fine!
- eventuelly also italics depend if chosen for currency EUR

Bart