+ Reply to Thread
Results 1 to 3 of 3

Conditional Format Dates in a Calender when Matches dates in a list

  1. #1
    Registered User
    Join Date
    09-18-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    66

    Conditional Format Dates in a Calender when Matches dates in a list

    Hi Guys, This has been bugging me for a bit now and I just can't sus it...

    I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.

    I have beside that a column for holidays, etc and then a another column for other events.

    When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).

    The formula I have been playing with (no success) is:

    =MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.

    The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...

    Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...

    I have attached the spread sheet that I am working on.

    Cheers in advance,

    Martin
    Attached Files Attached Files
    Last edited by Lungfish; 05-14-2009 at 06:25 AM. Reason: Problem Solved

  2. #2
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Conditional Format Dates in a Calender when Matches dates in a list

    try this

    =IF(ISERROR(MATCH(DATE($R$2,1,D8),$I$41:$J$65,0)),FALSE,MATCH(DATE($R$2,1,D8),$I$41:$J$65,0))

    it avoids the error if mach cant find a value

    correction

    =IF(ISERROR(MATCH(DATE($R$2,1,D8),$I$41:$I$65,0)),FALSE,MATCH(DATE($R$2,1,D8),$I$41:$I$65,0)>0)

    it must be a single row you where doing i:j
    Last edited by squiggler47; 05-14-2009 at 05:08 AM.
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  3. #3
    Registered User
    Join Date
    09-18-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    66

    Thumbs up Re: Conditional Format Dates in a Calender when Matches dates in a list

    HI,

    Thanks, That's Just the trick. I tried using the ISERROR formula before but like you say, it didn't take the blanks into account.
    (PS: I don't think my version of ISERROR was anything like what I was looking for).

    Works awesome.

    Regards to you Squiggler. Cheers...

    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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