Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill color by date | New Users to Excel | |||
color change after date | Excel Worksheet Functions | |||
Color and Date | Excel Discussion (Misc queries) | |||
Getting a fill color on a certain date | Excel Worksheet Functions | |||
I want any date 90 days or older from current date change color | Excel Worksheet Functions |