Date Formula in color

  • Thread starter Thread starter George Zindash
  • Start date Start date
G

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
 
Hi George!

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

--
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.
 
Back
Top