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 could create a calendar and use conditional formatting to highlight the dates. The conditional formatting would depend on how you setup your calendar.

 

[stylencia18]

How do I even start to do that? Is there a calendar function in Excel I can use?

 

[Scott T]

There are templates online that you can download or create one yourself.

12-Month Calendar - Office Templates

 

[stylencia18]

I was able to download a 12 month calendar template. Now how do I highlight my dates in the calendar? Do I select conditional formatting? Do I have to create a new rule?

Thank you.

 

[Scott T]

Yes, you would create a new rule. If the template you are using has excel dates you can select the days and in conditional formatting use this formula.

for example below you would select B6:H17

Where B6 is the first day and datelist!$A$1:$A$3 is your list
[TABLE="class: grid, width: 893"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Enter year:
[/TD]
[TD]2017
[/TD]
[TD]Week start:
[/TD]
[TD]S
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]2017
[/TD]
[TD][/TD]
[TD]FEBRUARY ->
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD="colspan: 2"]JANUARY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]S
[/TD]
[TD]M
[/TD]
[TD]T
[/TD]
[TD]W
[/TD]
[TD]T
[/TD]
[TD]F
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[TD]19
[/TD]
[TD]20
[/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD]22
[/TD]
[TD]23
[/TD]
[TD]24
[/TD]
[TD]25
[/TD]
[TD]26
[/TD]
[TD]27
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]29
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[/TR]
</tbody>[/TABLE]

=ISNUMBER(VLOOKUP(B6,datelist!$A$1:$A$3,1,0))

Which template did you use?

 

[My Aswer Is This]

You said earlier:
"(list goes on - there are approx. 600 dates on my list)"

If your using a 12 month calendar how can you have more then 600 dates you want highlighted when there are only 366 days in a 12 Month calendar?

Are you wanting all the dates highlighted with the same color?

Writing code to highlight 600 dates using CF I would think could be difficult.

Is there some type of reasoning to which dates are highlighted?

Like every "Tuesday"

 

[Scott T]

The list posted has dates from 2015 and 2016 so I assume it is multiple years. Unless the OP is creating calendars for past years there is no reason to have the old dates on the list.

 

[Perpa]

How do I even start to do that? Is there a calendar function in Excel I can use?

stylencia,
Here is a link to a similar calendar that I prepared which displays any dates in the MONTH and YEAR you select that are in the list you create in columns J and K:

Excel's Magic Calendar

There is a screen shot of the calendar is the last post of that thread (#24) if you want to see what it looks like before downloading anything. The download is available in post #18 of that thread.

The calendar only displays one month at a time, but it updates any events/holidays you have shown in the list for that month. You can save or print each month, then just select the next month from the dropdown in cell J4. It is a macro driven calendar, so you will have to enable macros at least the first time you open the file.
I hope this helps.
Perpa

 

[stylencia18]

I used the calendar I found here: Calendar creator (any year) - Office Templates Do I list my dates in the same file as the calendar and then apply the conditional formatting? I dont know what the formula for the conditional format would be. Sorry - I'm new at this and have no idea what I'm doing. I appreciate your help!!!!

Yes, you would create a new rule. If the template you are using has excel dates you can select the days and in conditional formatting use this formula. for example below you would select B6:H17 Where B6 is the first day and datelist!$A$1:$A$3 is your list [TABLE="class: grid, width: 893"] <tbody>[TR] [TD][/TD] [TD]A [/TD] [TD]B [/TD] [TD]C [/TD] [TD]D [/TD] [TD]E [/TD] [TD]F [/TD] [TD]G [/TD] [TD]H [/TD] [/TR] [TR] [TD]1 [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]2 [/TD] [TD][/TD] [TD]Enter year: [/TD] [TD]2017 [/TD] [TD]Week start: [/TD] [TD]S [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]3 [/TD] [TD][/TD] [TD]2017 [/TD] [TD][/TD] [TD]FEBRUARY -> [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]4 [/TD] [TD][/TD] [TD="colspan: 2"]JANUARY [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]5 [/TD] [TD][/TD] [TD]S [/TD] [TD]M [/TD] [TD]T [/TD] [TD]W [/TD] [TD]T [/TD] [TD]F [/TD] [TD]S [/TD] [/TR] [TR] [TD]6 [/TD] [TD][/TD] [TD]1 [/TD] [TD]2 [/TD] [TD]3 [/TD] [TD]4 [/TD] [TD]5 [/TD] [TD]6 [/TD] [TD]7 [/TD] [/TR] [TR] [TD]7 [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]8 [/TD] [TD][/TD] [TD]8 [/TD] [TD]9 [/TD] [TD]10 [/TD] [TD]11 [/TD] [TD]12 [/TD] [TD]13 [/TD] [TD]14 [/TD] [/TR] [TR] [TD]9 [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]10 [/TD] [TD][/TD] [TD]15 [/TD] [TD]16 [/TD] [TD]17 [/TD] [TD]18 [/TD] [TD]19 [/TD] [TD]20 [/TD] [TD]21 [/TD] [/TR] [TR] [TD]11 [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]12 [/TD] [TD][/TD] [TD]22 [/TD] [TD]23 [/TD] [TD]24 [/TD] [TD]25 [/TD] [TD]26 [/TD] [TD]27 [/TD] [TD]28 [/TD] [/TR] [TR] [TD]13 [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]14 [/TD] [TD][/TD] [TD]29 [/TD] [TD]30 [/TD] [TD]31 [/TD] [TD]1 [/TD] [TD]2 [/TD] [TD]3 [/TD] [TD]4 [/TD] [/TR] [TR] [TD]15 [/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]16 [/TD] [TD][/TD] [TD]5 [/TD] [TD]6 [/TD] [TD]7 [/TD] [TD]8 [/TD] [TD]9 [/TD] [TD]10 [/TD] [TD]11 [/TD] [/TR] </tbody>[/TABLE]

=ISNUMBER(VLOOKUP(B6,datelist!$A$1:$A$3,1,0))

Which template did you use?