Convert Listing into calendar and fills specified colours

[ngan202]

Hi all,

I joined for some help with a calendar I building.

What I want:
- perpetual calendar.
- takes the dates from a listing and converts it into specified colours that would shade itself in the date on the perpetual calendar

What I have done thus far:
-perpetual calendar


What I can't figure out:
I am trying to use conditional formatting to get the colours to show up on the calendar by the date.
I am also trying to get the date along with the colours to show up on the calendar and in the listing along the right side

I have attached the worksheet

Please someone help me with this or give me direction on how to get this accomplished

Thanks.

[Pete_UK]

I have posted a number of calendar files to threads on the forum. My approach is to have a list of "activities" on one sheet (showing dates and description of the events), and then on another sheet generate the calendar which shows a number of possible activities for each day. Here's an example from a few years ago where the OP used conditional formatting to colour-code the activities by type:

http://www.excelforum.com/excel-gene...entry-tab.html

Hope this helps.

Pete

[dflak]

I am assuming that this calendar is for a single person.

Here is one way to do it using a "shadow calendar" to the left of the main calendar and conditional formatting. The shadow calendar has to be on the same page since conditional formatting doesn't like off-page references. I only did the mock up for January only.

You can hide the columns containing the shadow calendar.

To get conditional formatting to work, we need to fill in the events which means we have to look them up from the data source which means we need a date value in every cell.

We calculate the date value using DATEVALUE($B$5 & " " & B9 & ", " & $B$2).

Then we can do a VLOOKUP against the Data Source (which I converted to an Excel Table called Table_Events).

VLOOKUP(DATEVALUE($B$5&" "&B9&", "&$B$2),Table_Events,3,FALSE)

Most of the lookups will fail, so we wrap an IFERROR around the formula.

IFERROR(VLOOKUP(DATEVALUE($B$5&" "&B9&", "&$B$2),Table_Events,3,FALSE),"")

And finally, don’t even think about it if the corresponding cell in the calendar is blank.

=IF(B9="","",IFERROR(VLOOKUP(DATEVALUE($B$5&" "&B9&", "&$B$2),Table_Events,3,FALSE),""))

This formula puts either a blank in the cell in the shadow calendar or it puts an event if there is one.

From there it is a matter of assigning a conditional on the main calendar based on the value in the corresponding cell in the shadow calendar. For example =AI9="Holiday" is formatted as light green.

[ngan202]

thanks for the help Dflak. For the help i have received, I thought i would
contribute back to the community and post a copy of the the result of that.
I really wasn't talented enough to make the perpetual calendar work for me so there had to be seperate tabs for the years but at least the data is able to populate the calendar

[dflak]

You want a perpetual calendar? This isn't mine. I stole it from the person cited at the bottom of the calendar. I did modify it for my holidays. Enter the year in at the top and it should compute.