Cell Color Based on the Day of the Month.

Hello, I am looking to format a cell color based on a day of the month, if "Today" is close to or past a certain day of the month.

I have a spreadsheet for my Budget. I have items like Credit-Cards, Utilities in each Row, and I have a Column for Budgeted, and Actual amounts.
I want to have the cell for (Discover, Visa, etc.) change color when it’s getting close to and past the due day.

I want it to turn yellow if "Today" is within say 6 days from now/today, and then turn Red if Today is within 2 days from now, and stay Red until something is entered in the “Actual” cell that is next to it.

(e.g. If Discover is due on the 10th or 10th Day of each month then the cell will turn Yellow on the 4th Day, and then turn Red on the 8th Day, and stay Red until I enter my Payment Amount in the “Actual” Column for that Row.)

I hope I have given a good explanation of what I am trying to do.

Thanks for the help.
Steve

Hello, Here is a sample worksheet.. I've just manually colored the cells.

You see Today's Date at the top of the sheet. and if the Discover and Car Loan payments were due in 2 days or less for both, they would be Red.
And Visa and Debt1 are due in 6 days or less so they are Yellow.
And Debt2 isn't due until the 18th so it the neutral color.

Thanks
Steve

hhhmm.. I was wondering if the formula/statement could be vague, as in, if the Discover Card was due on the 4th of every month.
Then the formula maybe
IF CURRENT DAY OF MONTH is within 6 days of the 4th Day THEN YELLOW and
IF CURRENT DAY OF MONTH is within 2 days of the 4th Day THEN RED..

hhhmm... Does this even make sense???
I’m trying to write it out and can’t seem to make it, make sense..

Thanks for the help

Hi Phil, Those work great.. Awesome.. Thanks!!!

Question, How do I get Both the 2-Day RED, and the 6-Day YELLOW rules to work on one cell at the same time?
I can only seem to get one rule work at a time. I'm testing by changing the date on my computer..

Thanks again.
Steve

Hi Steve.. Glad they're working for you
I've changed them a little bit so they're not reliant on getting a date from D1. I've also attached your workbook with test formulas entered in D19:E19 which I think work as you want. If you're happy with them as they are you can copy over the finalised formulas from here or from the worksheet. You'll see what I mean
Cheers
Phil
2B4-2After

Please Login or Register  to view this content.

6B4-2After

Please Login or Register  to view this content.

Hi Phil, thanks a ton... That's the effect I was looking for.

I had a feeling that it could be done, I just couldn't get there from here.
I've done a bit of VB coding in Access, using Access as a Front-end to a SQL Database Back-end.
Its been about 6 or 7 years, and you're right working dates was always finicky.

Thanks to everyone who contributed.

Thanks again Phil, "You-Da-Man"
Steve

All good Steve, glad it's working