+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting a range of cells based on number of weeks from given date

  1. #1
    Registered User
    Join Date
    09-27-2015
    Location
    London
    MS-Off Ver
    Office 13
    Posts
    20

    Conditional formatting a range of cells based on number of weeks from given date

    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
    Attached Files Attached Files
    Last edited by mrexcel27; 11-04-2017 at 04:15 PM.

  2. #2
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Conditional formatting a range of cells based on number of weeks from given date

    If you mention your desired result in sheet then it would be great because nobody wants to read a lot of lines.
    To attach worksheet Go advanced -> Manage attachments -> Choose file -> Upload
    Don't forget to Mention your desired result in the sheet..
    There should be sample data only, a lot of data creates confusion.

    Thanks & Regards

    Shivya

    http://excelvbatipsforbeginners.blogspot.in/

  3. #3
    Registered User
    Join Date
    09-27-2015
    Location
    London
    MS-Off Ver
    Office 13
    Posts
    20

    Re: Conditional formatting a range of cells based on number of weeks from given date

    Quote Originally Posted by shivya View Post
    If you mention your desired result in sheet then it would be great because nobody wants to read a lot of lines.
    Thanks for your reply.

    The TL;DR version is that I want to be able to copy and paste to replicate my conditional formatting to other cell ranges. But using absolute cell references in the conditional formatting (e.g $B$3) doesn't work because the same formula is referenced for each range and does not change when copied and pasted. However using non-absolute cell references in the conditional formatting (e.g B3) does not work because the whole range does not format as expected.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Conditional formatting a range of cells based on number of weeks from given date

    So at any cell of specific date, you need to refer to that date?
    I try to use define name: "Week", to refer to current date, then to return week count of that date
    The first row #1 CF is differ from the rest.
    Attached Files Attached Files
    Quang PT

  5. #5
    Registered User
    Join Date
    09-27-2015
    Location
    London
    MS-Off Ver
    Office 13
    Posts
    20

    Re: Conditional formatting a range of cells based on number of weeks from given date

    Quote Originally Posted by bebo021999 View Post
    So at any cell of specific date, you need to refer to that date?
    I try to use define name: "Week", to refer to current date, then to return week count of that date
    The first row #1 CF is differ from the rest.
    Thank you, this looks wonderful. I'm not immediately familiar with what you've done but this looks exactly like what I was trying to create. Will have a look through the conditional formats to see if I can work it out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rainbow conditional formatting for a range based of date...
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-31-2016, 03:41 PM
  2. Conditional Formatting - Colouring % of mulitple cells based on date range
    By yeahyeah93 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-13-2014, 09:22 AM
  3. [SOLVED] Conditional formatting dates based on date range and day
    By dancing-shadow in forum Excel General
    Replies: 4
    Last Post: 01-07-2014, 01:45 PM
  4. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  5. Replies: 8
    Last Post: 11-15-2011, 12:29 PM
  6. VBA Code Conditional Formatting based on number range
    By LMS0214 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2011, 03:08 PM
  7. Conditional formatting based on date range
    By RGB in forum Excel General
    Replies: 3
    Last Post: 05-23-2006, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1