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.

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

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!

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

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.

How about this?

thanks so much-- this is perfect!!

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

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

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.

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

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.

Look in Formulas>NameManager

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?

Hopefully I've got it right this time

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?

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