+ Reply to Thread
Results 1 to 16 of 16

Populate Calendar with List of Dates

  1. #1
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Populate Calendar with List of Dates

    Hello,

    I was hoping to get some help translating a single column of dates into calendar form.

    More specifically, I have a list of all dates over a certain period of time. Using conditional formatting, relevant dates over that time period have been highlighted in red. In turn, I would like to have those same dates highlighted in a traditional calendar format.

    Does this make sense?

    Thanks in advance for you help.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Do you have the calendar, or is that part of the task?

  3. #3
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    I was hoping to just use one of the Calendar templates available in Excel, but perhaps that is not feasible.

    I've attached the list of dates in the accompanying spreadsheet. I actually want to conduct this process separately for each of the six tabs, but if someone can just get me started, hopefully I'll be able to finish.

    Thanks so much for your help!
    Attached Files Attached Files

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Why 6 tabs? And will the calendar show all the years at once or have a year selector?

  5. #5
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    Ultimately, I want six separate 1999-2006 calendars for each fo the six tabs/

    It doesn't really matter to me if the calendar has a year selector or the entire time period is viewable all at once, although I suspect the former might be a little more practical.

  6. #6
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    How about this?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    thanks so much-- this is perfect!!

    is there any easy way to replicate this work for the other sheets?

  8. #8
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    also, i noticed that some of the months are blank?

  9. #9
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Yes.

    First create a name similar to what I have done for name Call_Dates_1 for the other sheets.

    Then copy Calendar_1 over and do a global Find and Replace on Call_Dates_1 to the new names.

  10. #10
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Uumm! I messed that up a bit. Try this revision
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    please pardon my density on this, but what exactly did you name 'Call_Dates_1'? I've copied over calendars for each additional sheet, but I'm not sure what text I am replacing?

    thanks again for all your help-- a real life saver.

  12. #12
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Look in Formulas>NameManager

  13. #13
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    I just realized that the second column of months for each year (Feb-May-Aug-Nov) has the identical highlighting scheme as the first column (Jan-Apr-Jul-Oct). I suspect this is due to typo, but I can't figure out where the mistake occurred! In the conditional formatting?

  14. #14
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Hopefully I've got it right this time
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    09-29-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Populate Calendar with List of Dates

    this is great.

    one last question: if i want to denote holidays on the calendars, how would i do that? i created a new sheet with all the dates of relevant holidays. i then created a new defined name called 'holidays' for the data. what are the conditional formatting rules i have to introduce to make this happen?

  16. #16
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populate Calendar with List of Dates

    Check the rules for the other dates I have added. It will be similar.

+ 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