+ Reply to Thread
Results 1 to 8 of 8

Conditional Format for Date Ranges in Calendar

  1. #1
    Registered User
    Join Date
    12-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    30

    Conditional Format for Date Ranges in Calendar

    Hi All,

    I have a custom annual calendar which I am looking to highlight with scheduled items. For each of these there is and start and end date laid out in columns as shown below.

    Item Start Date End Date
    Party 14 May 2013 15 May 2013
    Holiday 20th May 2013 26th May 2013

    For a single item I would simply use the following conditional format, with A1 being the cell containing a date:

    =AND(A1>=Start_Date,A1<=End_Date)
    However for multiple items, I want to use a named range for 'Start_Date' and 'End_Date' so that they all use the same formatting.

    Any suggestions?

    Thank you!

    Oliver

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Format for Date Ranges in Calendar

    You have just to adjust your range, using same formula as in rowA..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    12-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    30

    Re: Conditional Format for Date Ranges in Calendar

    I realise I didn't clarify that all too well.

    The name ranges currently only point to 1 cell each:

    Start_Date = 14 May 2013
    End_Date = 15 May 2013
    Now I dont want to do this for every item in my list as I am looking at quite a few.

    So I've changed my named ranges to reference the columns:

    Start_Dates = A:A
    End_Dates = B:B
    Now for the conditional format I need to do the same, but check for each row in the named ranges.

    Oliver

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Format for Date Ranges in Calendar

    I think it's better to upload a small sample workbook showing the expected results..

  5. #5
    Registered User
    Join Date
    12-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    30

    Re: Conditional Format for Date Ranges in Calendar

    Calendar Con Format.xlsx

    Calendar is attached. Formatting I am referening is in the Calendar tab, D5:AA51, Green filler.

    Oliver

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Format for Date Ranges in Calendar

    Does this works?

    =SUMPRODUCT((Title=E5)*(D5>=Start_dates)*(D5<=End_Dates))>=1

  7. #7
    Registered User
    Join Date
    12-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    30

    Re: Conditional Format for Date Ranges in Calendar

    That it certainly does! Hadn't thought of using sum product this way, quite an insight.

    Greatly appriciated. Thanks!

    Oliver

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Format for Date Ranges in Calendar

    ......................

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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