date in color
 

I'm working on a db of accounts recevable. All accounts are due on the first
of the month.
I will enter dates when paid, what I would like to do is change the color
of the date in the cell "If paid befor the First=green, First to
fifth=black, after fifth=red " for each month. The formula's I tryed will
eather work till it has to change a color ( then it stays with that color)
or it will see Feb. as "late" becouse it referes back to Jan. I'm pretty
sure I have to do each month in a different formula.
Thank You for any help you can give.
Rusty


a b c d e f g h i j
john Tim Mary
5 Jan 1/1 1/1 1/1
6Feb. 1/2 1/2 3/2
7Mar 1/3 6/3 1/3
8Apr
9May
10Jun
11Jul
12Aug

date in color
 

Hi George!

No need to post separately to different groups (but see below). Answer
given in worksheet.functions was:

Here's the two Conditional Formatting formula:

(I've used B8 as the cell containing the date paid)
Also I've assumed dates entered as dates

Condition 1
=B8<DATE(YEAR(A8),MONTH(A8)+1,1)

Condition 2
=B8DATE(YEAR(A8),MONTH(A8)+1,5)

You don't need a third formula because the default is (assumed to be)
the normal color of black and will cover all cases other than dates
before 1st and after 5th.

If that's a problem then one way is

Condition 1
=OR(ISTEXT(B8),ISBLANK(B8))
Format white on white
Condition 1
=B8<DATE(YEAR(A8),MONTH(A8)+1,1)
Format green
Condition 2
=B8DATE(YEAR(A8),MONTH(A8)+1,5)
Format red

<<End Answer

But it could be that you might prefer a VBA answer and with more than
3 conditions that would be the way to go.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"George Zindash" wrote in message
. ..
I'm working on a db of accounts recevable. All accounts are due on
the first
of the month.
I will enter dates when paid, what I would like to do is change the
color
of the date in the cell "If paid befor the First=green, First to
fifth=black, after fifth=red " for each month. The formula's I tryed
will
eather work till it has to change a color ( then it stays with that
color)
or it will see Feb. as "late" becouse it referes back to Jan. I'm
pretty
sure I have to do each month in a different formula.
Thank You for any help you can give.
Rusty


a b c d e f g h i
j
john Tim Mary
5 Jan 1/1 1/1 1/1
6Feb. 1/2 1/2 3/2
7Mar 1/3 6/3 1/3
8Apr
9May
10Jun
11Jul
12Aug

date in color
 

Sorry for the post in the two groups

Thanks for your reply it got me close enough to figure it out.
It should be:
Condition 1
=B8<DATE(YEAR(A7),MONTH(A7)+1,1)

Condition 2
=B8DATE(YEAR(A7),MONTH(A7)+1,5)

I don't know why it has to refer back to A7 for a date in A8 but it looks
like it will work. That's what you get when you give a computer to a diesel
mechanic...
Thanks again.



"Norman Harker" wrote in message
...
Hi George!

No need to post separately to different groups (but see below). Answer
given in worksheet.functions was:

Here's the two Conditional Formatting formula:

(I've used B8 as the cell containing the date paid)
Also I've assumed dates entered as dates

Condition 1
=B8<DATE(YEAR(A8),MONTH(A8)+1,1)

Condition 2
=B8DATE(YEAR(A8),MONTH(A8)+1,5)

You don't need a third formula because the default is (assumed to be)
the normal color of black and will cover all cases other than dates
before 1st and after 5th.

If that's a problem then one way is

Condition 1
=OR(ISTEXT(B8),ISBLANK(B8))
Format white on white
Condition 1
=B8<DATE(YEAR(A8),MONTH(A8)+1,1)
Format green
Condition 2
=B8DATE(YEAR(A8),MONTH(A8)+1,5)
Format red

<<End Answer

But it could be that you might prefer a VBA answer and with more than
3 conditions that would be the way to go.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"George Zindash" wrote in message
. ..
I'm working on a db of accounts recevable. All accounts are due on
the first
of the month.
I will enter dates when paid, what I would like to do is change the
color
of the date in the cell "If paid befor the First=green, First to
fifth=black, after fifth=red " for each month. The formula's I tryed
will
eather work till it has to change a color ( then it stays with that
color)
or it will see Feb. as "late" becouse it referes back to Jan. I'm
pretty
sure I have to do each month in a different formula.
Thank You for any help you can give.
Rusty


a b c d e f g h i
j
john Tim Mary
5 Jan 1/1 1/1 1/1
6Feb. 1/2 1/2 3/2
7Mar 1/3 6/3 1/3
8Apr
9May
10Jun
11Jul
12Aug