Using "IF" statement and conditional formatting on a Range of Cells

[vincet2015]

Hello,

First off, let me say that I not that familiar with “IF” formulas and conditional formatting. Any help would be greatly appreciated.

Here is what I am trying to do. I created a bill calendar/budget which ensures that I pay my bills on time. I pay my bills on my payday, which is every Friday. The calendar (which is attached) is projected out for roughly 6 months. The dates shown on the calendar are the Friday date, 4 days after the date, and 6 days after that. So in other words, any bill that is due between 4 -10 days after the Friday, need to be paid on that Friday. For example, on my spreadsheet on 09/25/15 (cell c8), any bills that are due between 09/29/15 (cell c12) and 10/05/15 (cell c14) need to be paid on 09/25/15.

The bill due dates are listed to the right of the calendar.

When I create a 6 month calendar, I have to manually look at the due dates and copy and paste the ones that fall within the 4-10 range into the calendar. This as you can imagine in a time consuming process. I would like setup formulas in the calendar so that this process is done automatically.

So for example; in looking at the spreadsheet, cells k-m list the bills that are due within the 4-10 day range. You will see that Genworth, Recology, Rent, ADT, and American General all fall within 09/29/15 and 10/05/15.

The spreadsheet I provided only goes for 3 weeks, not 6 months.

Thanks in advance.

Vince

[daffodil11]

How do you reconcile bills that are due on the 31st of each month with months that have less than 31 days?

[Bernie Deitrick]

Takes a little extra, with formulas below the week's date (hidden using ;;; format) Copy and paste the block of cells below to extend the calendar.

Calendar Test.xlsx