How to change colour of a date in a calendar made in a pivot table as per a specific value

Hello all!

I've learned recently that I know nothing about the capabilities of excel. I've managed to get this far alone, but I'm stuck and hope you lovely folks will be able to point me in the right direction.

I recently offered to help my SO make an easier excel file to track vacation days of his colleagues (the one the company offers was made back in 1995 and is horrendous. They plan on updating it never.) The criteria for this project is to have two worksheets:

1. A pivot table of the yearly calendar (calendar.jpg and pivot.jpg below)
2. Data used for the pivot table. (data.jpg below)

For each day of the month, a max of only 2 people are allowed to take a day off. In an effort to make it easier for his colleagues to determine when they can take time off, my SO would like the days in the calendar to remain "white" if there are two slots available, "yellow" if only one slot is available, and "red" if no slots are available for vacation.

In my data sheet, he will list their names in "First slot" and "second slot" on the days they select. I have made it that depending on the status of each cell, a number will appear in the corresponding columns "slot 1" and "slot 2", where if blank, the value is "0", if there is text, the value is "1." I totaled these in the "Total" column.

I am not sure if I'm going the wrong direction, but my thought process is that if the result for a specific date in the "Total" column was "0", then the date would remain "white", if "1", it would be "yellow" and if "2" then "red", meaning no one else can take that day off... but I have no clue how to do that.

Here is what I have right now:

calendar.JPG
pivot.JPG
data.JPG

Any help or suggestions would be greatly appreciated.

ETA: I just realized, I could have just uploaded the excel file (DOH!): Attachment 280561

First point: it's much quicker and easier to help if you attach a workbook, rather than JPGs. Saves me recreating your data...

Now. There are several ways to skin this particular cat. I've gone with your original data structure and pivot format, though, to show one possibility. I've added a second Value field [Sum of Total] to the pivot table to give a relative reference containing the count of holiday slots used for each day, then applied some conditional formatting based on that. Then I've hidden those 'helper' columns. It's inelegant, but it works. See attachment.

CalendarCF.xlsx

Great - glad it does what you need.

You can mark the thread 'solved' now