I am trying to create a calendar template in Excel which will help me in planning for events in the year 2018. I would like to use the calendar to display a certain colour based on the number of weeks between today's date and all future dates in 2018.
The colour of each range of cells representing a date will change the closer the date gets as I need to start considering bookings for six months (26 weeks) ahead and ensure that bookings are completed three months (12 weeks) ahead. Thus I hope to have a colour scale that will change every two weeks. I believe I should be able to do this with conditional formatting.
The range of cells representing a particular date are (for example) A2:B7. The date is in A2 (merged with B3 so it fits across two columns). The number of days between today and this date is in A3 calculated with the formula =A2-TODAY(). The number of weeks between today and this date is in B3 calculated with the formula =A3/7.
I wish to set up conditional formatting for the range A2:B7 based on the number in B3 and then replicate this for other dates when I have it working.
I have selected cells A2:B7. Opened conditional formatting and set up my formats based on 'Use formula to determine which cells to format'. In the 'Format values where this formula is true' have typed =$B$3<=12 and made the format red fill. I have added more colour formats based on the number of weeks being <=14, 16, 18, 20, 22, 24 and >24. Each rule has the stop if true box checked.
For this single range, this works. Changing the date in A2 to a future date more than 12 weeks ahead results in the colour of the whole cell range changing based on the date input and the number of weeks displayed in B3.
OK, now I need to replicate this for other ranges representing other dates. But the conditional formatting copied over to the next day (range C2:D7) is still formatting based on B3 if I leave the absolute cell references in the conditional formatting rules. So before copying the conditional formatting for A2:B7, I remove the absolute $ signs from =$B$3<=12 and all other rules to give me =B3<=12 etc.
On testing this now, this doesn't work as expected. Changing the date in A2 to a future date more than 12 weeks ahead results in only cell A2 changing colour, all the other cells remain red.
It would take me ages to go through and change each of the conditional formatting rules for each date manually. Surely there is a way to copy and paste it so that it works?
Have attached the workbook so others can see what I mean.
In A2:B7 is the conditional formatting based on the absolute $B$3 value
In A8:B13 is the conditional formatting based on the relative B9 value
Bookmarks