+ Reply to Thread
Results 1 to 5 of 5

Conditional format cell in Calendar based on dates in another spreadsheet

  1. #1
    Registered User
    Join Date
    01-29-2012
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    8

    Conditional format cell in Calendar based on dates in another spreadsheet

    Hi

    I wanted to conditional format dates in calendar, based on Date of arrival/ appointment in another spread sheet.

    Ideally would Like to format Date of starting & end of event in different colour & the continuum in a different colour. eg 2016 relates to Calendar2016

    Currently using calendar from

    https://www.vertex42.com/ExcelTempla...-calendar.html

    (Using this Calendar as allows me to generate a new one every year)

    Open to any suggestion for calendar or change data in spreadsheet

    Many thanks
    Attached Files Attached Files
    Last edited by luckyearl; 08-07-2017 at 12:00 PM.

  2. #2
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Conditional format cell in Calendar based on dates in another spreadsheet

    KS,

    You may use this conditional rule

    By highlight the all calendar date, (ie
    =$B$5:$AF$10,$B$14:$AF$19,$B$23:$AF$28

    if not it will highlight the month header.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thank you,
    Attached Files Attached Files
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]
    ;and
    Click *Add Reputation to thank anyone solved your question.

  3. #3
    Registered User
    Join Date
    01-29-2012
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional format cell in Calendar based on dates in another spreadsheet

    Thank you ! it does a perfect job

    wud be grateful if you cud explain how the formula works

    Have a great day

  4. #4
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Conditional format cell in Calendar based on dates in another spreadsheet

    =SUMPRODUCT(--(B5>='2017'!$B$2:$B$7),--(B5<='2017'!$E$2:$E$7))

    since the conditional format is start by B5, hence, B5 in the formula mean the current cell.

    the >= and <= are mean to between

    SUMPRODUCT is a Function that use as IF function in here since conditional formatting is not able to use array formula,

    the -- change the result to number, and the excel assume 1 = TRUE and0 = FALSE

    Hope it clarify.

    PS:you may copy the formula in the cell other than B5 and go to formula evaluate to see how it work. (path to formula evaluate is written below)

  5. #5
    Registered User
    Join Date
    01-29-2012
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Conditional format cell in Calendar based on dates in another spreadsheet

    Quote Originally Posted by BoredWorker View Post

    (path to formula evaluate is written below)
    Thank you very much, based on this managed to change formatting for arrival & departure dates too. You are kind. Have a good day

+ 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. [SOLVED] Conditional Format Calendar if Date Matches Range of Dates in Other Cells
    By blackburnsexcel in forum Excel General
    Replies: 7
    Last Post: 06-23-2016, 01:18 PM
  2. Conditional format based on dates
    By rhodestransit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-11-2015, 01:17 PM
  3. Replies: 4
    Last Post: 09-19-2014, 08:10 AM
  4. [SOLVED] conditional format based on dates.
    By 11ktran44 in forum Excel General
    Replies: 3
    Last Post: 03-26-2014, 12:55 PM
  5. Conditional Format Cell based on Dates in Other Cells
    By kortekn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2013, 11:50 AM
  6. Replies: 10
    Last Post: 10-18-2010, 06:42 AM
  7. Conditional Format matching Dates,calendar
    By ufo_pilot in forum Excel General
    Replies: 3
    Last Post: 07-31-2006, 08:05 AM

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