Highlight cells based on a specific day of the month

Closed
trentseibel Posts 1 Registration date Thursday March 6, 2014 Status Member Last seen March 6, 2014 - Mar 6, 2014 at 01:23 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 10, 2014 at 12:07 PM
Hi there. I have a bill spread sheet that is layed out pretty simple. I basically have all of my bills listed in the order they are due each month. The first column has the day of the month that the bill is due. For example, all bills due on the 1st of the month simply have the number 1 in that column. All bills due on the 22nd have a 22 in that column.

What I am looking for is a way to make it so that the blocks turn yellow as the current day of the month is within 7 days from the day of the month in each block, and red on the day of the month/after the day of the month has passed.

In short, yellow when the day is 7 days out and red when it is due and after it is due. It would be even better if it would loop. So on the 25th, all days after the 25th and the 1st, 2nd would be yellow. Thank you in advance.

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 10, 2014 at 12:07 PM
Hi Trentseibel,

You probably figured you have to use conditional formatting and are wandering what the formula's would be, right?

1) =A1-DAY(TODAY())<=0 [red]
2) =AND(A1-DAY(TODAY())>=1,A1-DAY(TODAY())<=7) [yellow]

For your second question, how do we know which month it is?

Best regards,
Trowa
0