Creating a calendar in Excel and highlighting certain dates

[stylencia18]

Good morning!

I have a list of dates in Excel that I would like to highlight in a calendar.

Ex) January 1, 2015
January 29, 2015
February 9, 2015
March 12, 2015
March 13, 2015
April 1, 2015
February 15, 2016
(list goes on - there are approx. 600 dates on my list)

Does Excel have a calendar where I can only highlight the dates on my list?

Thank you!!!!!!!!!!!!!!

Amanda

 

[Scott T]

You can put your list of dates off to the side or on another sheet(tab) in the same file.

With the template you used select C4:AG27

Select Conditional Formatting from the ribbon
Select New Rule
Select Use a formula to determine which cells to format

use this formula change datelist!$A$1:$A$10 to match where you have your dates. To make it easier you can enter the formula in a blank cell and then copy it, that way you can select cells with the mouse.

=ISNUMBER(VLOOKUP(C4,datelist!$A$1:$A$10,1,0))

Select the formatting you want by clicking on the format button
click OK

 

[stylencia18]

Wow this is great!!!! I changed C4 in your formula to C2.

Thank you so very much! You rock

You can put your list of dates off to the side or on another sheet(tab) in the same file.

With the template you used select C4:AG27

Select Conditional Formatting from the ribbon
Select New Rule
Select Use a formula to determine which cells to format

use this formula change datelist!$A$1:$A$10 to match where you have your dates. To make it easier you can enter the formula in a blank cell and then copy it, that way you can select cells with the mouse.

=ISNUMBER(VLOOKUP(C4,datelist!$A$1:$A$10,1,0))

Select the formatting you want by clicking on the format button
click OK

 

[stylencia18]

One follow up question slightly changing my original question. I have data with a bunch of dates for different people. I would like to apply the conditional formatting on that same calendar for each person separately.

Marty 1/1/2015
1/2/2015
1/3/2015
1/4/2015
1/5/2015

Amanda 2/2/2015
2/3/2015
2/7/2015
2/8/2015

(I have approx. 350 different people with dates associated with them). Can I do a pivot table/slicer and apply the conditional formatting to the calendar for each person separately? This will make it easier to toggle between each person.

As of right now I can only look at the calendar for each person once at a time and then I have to change the conditional formatting formula each time to select the appropriate rows for each person.

 

[Scott T]

You could do something like this
Sheet datelist
[TABLE="class: grid, width: 512"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 64, bgcolor: transparent"]G[/TD]
[TD="width: 64, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3/1/2015
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Marty
[/TD]
[TD="width: 64, bgcolor: transparent"]Amanda
[/TD]
[TD="width: 64, bgcolor: transparent"]Beth
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3/2/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/1/2015
[/TD]
[TD="bgcolor: transparent, align: right"]2/2/2015
[/TD]
[TD="bgcolor: transparent, align: right"]3/1/2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3/3/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/2/2015
[/TD]
[TD="bgcolor: transparent, align: right"]2/3/2015
[/TD]
[TD="bgcolor: transparent, align: right"]3/2/2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3/4/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/3/2015
[/TD]
[TD="bgcolor: transparent, align: right"]2/7/2015
[/TD]
[TD="bgcolor: transparent, align: right"]3/3/2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]3/5/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/4/2015
[/TD]
[TD="bgcolor: transparent, align: right"]2/8/2015
[/TD]
[TD="bgcolor: transparent, align: right"]3/4/2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]3/6/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/5/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/5/2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]3/7/2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/6/2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/7/2015
[/TD]
[/TR]
</tbody>[/TABLE]

In A1 use this formula. This is an array formula and must be committed with CONTROL+SHFT+ENTER not just enter. If done correctly Excel will put {} around the formula. Then copy down. Make sure you copy it down far enough so that it pulls all the dates for the select name.

=IFERROR(INDEX($F$2:$H$8,SMALL(IF(Calendar!$AJ$1=$F$1:$H$1,ROW($H$2:$H$8)-ROW($H$2)+1),ROWS($A$1:A1)),MATCH(Calendar!$AJ$1,$F$1:$H$1,0)),"")

On the sheet with the Calendar in a cell, I used AJ1, from the data ribbon select data validation
Allow: List
Source: this is the list of names

=datelist!$F$1:$H$1

You will now have a drop down list of names to pick from and the date list used in conditional formatting will change based on the name in AJ1.

To handle blank cells in the range the conditional formatting formula will need to be updated.

=AND(ISNUMBER(VLOOKUP(C4,datelist!$A$1:$A$20,1,0)),C4<>"")