+ Reply to Thread
Results 1 to 7 of 7

Visual Calendar from List

  1. #1
    Registered User
    Join Date
    09-20-2015
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    16

    Visual Calendar from List

    I've been attempting to find the resolution for this for a few days now and coming up short.

    I have a large data sheet that pulls information into a table. In this table there are quite a few columns each with specific data (names, locations, etc...) and included in that data are date ranges. What I need to create is a visual calendar view that pulls the start and end date and plots them into a visual. This would need to be specific to the name on the left so if "employee 1" has an activity with a start date of 3/4/19 and an end date of 3/25/19 I need it to show these in a different color. In the image below my "data" is off to the right as an example. It would be on a different sheet within the same workbook in reality.

    visual_cal.JPG

    Hopefully this makes sense, please let me know if there are any additional questions I can help with.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Visual Calendar from List

    I

    Use this formula in Conditional Formatting for all cells
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note: Folha2!$B$6:$D$12 is the table of (names, start date, end date)

    See the file for clarification
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-20-2015
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Visual Calendar from List

    That works great, can it be modified to show a different conditional format for a different set of dates in separate columns?

    If not I can probably work around that with what you've provided here.

    Thanks so much!

  4. #4
    Registered User
    Join Date
    09-20-2015
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Visual Calendar from List

    Sorry for the double post.

    I've ran into an issue with the conditional formatting above. It seems to only return the first occurrence of someones name. So if "Name 1" appears a couple of times in my data it only plots the first range. I need it to track them all, none of the dates overlap.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Visual Calendar from List

    Try replacing your conditional formatting formula with: =SUMPRODUCT((Folha2!$B$6:$B$12=$B4)*(Folha2!$C$6:$C$12<=C$3)*(Folha2!$D$6:$D$12>=C$3))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    04-03-2019
    Location
    London
    MS-Off Ver
    Excel 365
    Posts
    6

    Re: Visual Calendar from List

    Hi JeteMc, I've been searching for an answer to almost the exact same problem, except my calendar is on a different sheet and uses objects to change the month and year. Is it possible to adapt the formula for that situation? If so, would the conditional formatting go on the sheet with the data or on the sheet with just the calendar on it?

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Visual Calendar from List

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    PS. Send me a PM once the new thread is opened so I'll be sure to look.

+ 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. Calendar Template for "visual budget"
    By littlewing52 in forum Excel General
    Replies: 3
    Last Post: 10-25-2018, 09:19 PM
  2. Replies: 3
    Last Post: 07-29-2014, 01:20 PM
  3. Replies: 4
    Last Post: 03-20-2014, 06:37 PM
  4. Calendar-based visual dashboard from project progress data
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2012, 11:24 AM
  5. Convert inputted dates to visual calendar
    By fugro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-19-2011, 11:49 PM
  6. Replies: 1
    Last Post: 09-13-2005, 07:06 AM
  7. Replies: 1
    Last Post: 07-10-2005, 12: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