+ Reply to Thread
Results 1 to 9 of 9

Conditional Format a Calendar based on Date Values and Text String

  1. #1
    Registered User
    Join Date
    10-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    4

    Unhappy Conditional Format a Calendar based on Date Values and Text String

    Hello All -

    I have manually created a calendar in a spreadsheet with Weeks as columns as specific tasks in rows

    I have another worksheet that has a bunch of data including the specific tasks and "planned date".

    I would like the calendar to automatically conditional format the week if there is a planned activity for that specific task.

    Ultimately, the logic I need to use is as follows:

    If In WorkSheet Column A, the Value (date) >=Date1 <=Date 2
    And IF WorkSheet A, Row B, Row Text = "Text String"
    Conditional Format WorksheetB (the calendar) the specific row/column = Green

    I would need this formula across each week (ideally without having to manually manipulate each (>=date 1 <=date 2)

    I have tried building this out but realized I am out of my depth. Anybody done this before?

    Thanks

    Dan
    Attached Files Attached Files
    Last edited by djwhite2710; 10-23-2019 at 09:40 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: Conditional Format a Calendar based on Date Values and Text String

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Do not try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

    I'll take a look at it tomorrow, as it is getting late here.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Format a Calendar based on Date Values and Text String

    Thanks Pete - I have attached a sample file that is the framework of what I am trying to build.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: Conditional Format a Calendar based on Date Values and Text String

    There are a few problems with your calendar layout. You have dates which are formatted to display the month and year on row 2, but the dates are all the 19th of the month - why not make them 1st of the month?

    Also, on row 3 you have Wk1 to Wk4 for every month, but there are more than 4 weeks in a month - if you use 4-week months then there are 13 such months in a year, with one day left over, so you would need to account for this discrepancy.

    It might be better to generate actual dates in your calendar (with narrower columns), and you could omit Saturday and Sunday if these are of no use to you. You can also arrange the formatting to spread over the 5 working days in the week where an event is scheduled.

    It might also be useful to you to have two drop-downs in the calendar sheet where you can specify the start month and the year, and the display automatically adjusts for one year from that starting point.

    Hope this helps.

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: Conditional Format a Calendar based on Date Values and Text String

    I hope my earlier comments have not put you off. I've set this up for you, along the lines that you showed in your sample file, but using individual dates.

    Are you interested in seeing it? (Ref: 1351)

    Pete

  6. #6
    Registered User
    Join Date
    10-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Format a Calendar based on Date Values and Text String

    Hi Pete - not put off at all. It was helpful. I would be interested in seeing any sample files you had setup. Good call on the weeks/days.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: Conditional Format a Calendar based on Date Values and Text String

    OKay, file attached.

    You can specify the month (by number) and year where you want the display to start using cells B2 and B3 respectively - the display will automatically adjust, giving just over a year across the columns.

    I've arranged it so that the first week displayed includes the 1st of the month that you have selected as above, but the display only shows the days from Monday to Friday, so if the 1st of the month occurs on either a Saturday and Sunday, this means that you will have a few days from the end of the previous month. These are shown red, to avoid any confusion. A similar thing happens at the end of the display (i.e. up to 2 weeks into the next year).

    The display shows a 5-day window for each event, i.e. Monday to Friday of the week in which the event occurs will be highlighted on the appropriate row. For this to happen, I have added this formula to cell H4 of your Log sheet:

    =IF(B4="","-",LEFT(B4)&"_"&F4-WEEKDAY(F4,2)+1)

    I've copied this down to row 100, in order to accommodate new data being added, but you can copy it further if you wish - the hyphens help to show where the formula is active.

    So, you just add data to the table in the Log sheet (in any order), and the appropriate part of the calendar display will be highlighted. You can adjust the starting point of the display using B2 and B3.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-22-2019
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Format a Calendar based on Date Values and Text String

    This is a huge help, thank you so much

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: Conditional Format a Calendar based on Date Values and Text String

    Glad you like it - feel free to ask any questions about how it works.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Conditional format based on 2 text values
    By ACrossley1 in forum Excel General
    Replies: 1
    Last Post: 08-14-2019, 05:58 AM
  2. Conditional Format Based on Date and Source of Text
    By Mister Blutarsky in forum Excel General
    Replies: 3
    Last Post: 04-03-2018, 10:32 AM
  3. [SOLVED] Conditional format cell in Calendar based on dates in another spreadsheet
    By luckyearl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2017, 03:38 AM
  4. [SOLVED] Conditional Format for Date Ranges in Calendar
    By Oliver Vistisen in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-13-2013, 08:09 AM
  5. [SOLVED] Convert a general format string to calendar date in Excel 2010 vba?
    By Kuriakos in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-18-2012, 11:44 AM
  6. Replies: 10
    Last Post: 10-18-2010, 06:42 AM
  7. Conditional Format based on Text String
    By jpkeller55 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2005, 11:02 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