Highlight cells based on a specific day of the month

[Closed]
Report
Posts
1
Registration date
Thursday March 6, 2014
Status
Member
Last seen
March 6, 2014
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
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

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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