date in color

[George Zindash]

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

 

[Norman Harker]

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
=B8>DATE(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
=B8>DATE(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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Rusty]

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
=B8>DATE(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.