+ Reply to Thread
Results 1 to 6 of 6

conditional formatting of cells with quarters based on date ranges in other cells

  1. #1
    Registered User
    Join Date
    08-02-2013
    Location
    usa
    MS-Off Ver
    Excel 2011
    Posts
    8

    conditional formatting of cells with quarters based on date ranges in other cells

    EXAMPLE FILE:
    example.xlsx

    I have a file with:
    - 2 columns: Column 1 = start date, Column 2 = end date
    - one column per quarter (Column3 = Q3 2013, Column 4= Q4 2013, etc)

    I need to color cells in quarter columns grey if the quarter in a particular column falls out of the date range in Column 1 and Column 2.
    For example: Col1=8/1/13, Col2=9/1/13
    since the date range is in Q3, cell in Q3 column will stay white, while Q4 column will be colored grey since Q4 is outside of the specified date range.

    The date range could span multiple quarters.

    Please see attached example.

    Any help would be greatly appreciated!

  2. #2
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: conditional formatting of cells with quarters based on date ranges in other cells

    This is the solution in general terms, tell me if you need more details:

    select the range that you want to color fill
    click conditional formatting and use a formula. if column A needs to be colored based on column B, type something like =B1=[your formula]
    now excel automatically puts $'s around your B1 -> $B$1
    remove the second $ sign (-> $B1)
    now cell A1 is filled according to B1, A2 according to B2 etc.
    When I say semicolon, u say comma!

  3. #3
    Registered User
    Join Date
    08-02-2013
    Location
    usa
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: conditional formatting of cells with quarters based on date ranges in other cells

    Thanks a lot, L-Drr, for your help. I am looking for more details.

    I have column headers as yearly quarters (Q1, Q2, I attached the file in my question). How do I figure out in the formula if dates from other columns fall within which quarter? How do I then specify in the formula to color quarter cells that's not within the date range?

  4. #4
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: conditional formatting of cells with quarters based on date ranges in other cells

    I've taken a closer look at your file, and I was wondering the following: are all the combinations of start dates and end dates going to be one whole month, in chronological order? Because in that case it's just a matter of manually coloring the cells of 12 months and 4 quarters and copying that.

    If not, I can fix your problem if we can create one extra row that contains the dates of the quarters. But first let me know is the above is true

  5. #5
    Registered User
    Join Date
    08-02-2013
    Location
    usa
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: conditional formatting of cells with quarters based on date ranges in other cells

    Unfortunately, the start and end dates are not going to be one whole month in chronological order, it can span several quarters, so it's not that straight forward as manually coloring.

    THanks, L-Drr, for your help.How would you suggest to do it?

  6. #6
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: conditional formatting of cells with quarters based on date ranges in other cells

    Then what is the exact condition that you require? Do the start-and-end date have to be within the Quarter, or does the Quarter have to be within the start-and-end date?
    And what are the start dates of your quarters? January 1st, April 1st, etc?
    Last edited by L-Drr; 08-06-2013 at 02:30 PM.

+ 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. formatting cells with quarters based on date ranges
    By nheb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-09-2013, 11:11 AM
  2. How to mark cells based on date in other cell using conditional formatting
    By mharsvik in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2012, 12:33 PM
  3. Conditional Formatting Cells to reference Date Ranges
    By Vic4306623 in forum Excel General
    Replies: 1
    Last Post: 12-03-2011, 07:13 AM
  4. Replies: 4
    Last Post: 09-27-2011, 04:16 AM
  5. Replies: 15
    Last Post: 07-23-2008, 06:59 AM

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