## Excel calendar (vba)

This calendar let´s you schedule events on the data sheet. Select a cell (calendar date) and events on that day are shown below the calendar.

Today´s date is highlighted yellow. Days with one or more events are also highighted.

### How I created the calendar

**Table of Contents**

- Calendar
- Conditional formatting
- Formulas
- Visual basic for applications
- Download excel *.xlsm file

### Drop down lists

**Select year**

- Select cell B2
- Go to tab "Data"
- Click "Data Validation" button

- Allow: List
- Source:2012,2013,2014,2015,2016
- Click OK

**Select month**

- Select cell E4
- Go to tab "Data"
- Click "Data Validation" button
- Allow: List
- Source:January, February, March, April, May, June, July, August, September, October, November, December
- Click OK

### Headers

Type Year: in cell B2, Month: in cell E2 and so on...

### Calculating dates (formula)

- Select cell B5

- Formula:
=DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1)-WEEKDAY(DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1),2)+1
- Select cell B6
- Formula:
=B5+1
- Copy cell B6 (Ctrl + c)
- Select cell range D5:H5
- Paste (Ctrl + v)
- Select cell B6
- Formula:
=B5+7
- Copy cell range C5:H5
- Select cell range C6:H6
- Paste (Ctrl + v)
- Copy cell range B6:H6 (Ctrl + c)
- Select cell range B7:H10
- Paste (Ctrl + v)

### Conditional formatting

- Select cell range B5:H10

- Go to tab "Home"
- Click "Conditional formatting" button
- Click "New Rule..."
- Click "Use a formula to determine which cells to format"
- Format values where this formula is true:

=B5=TODAY() - Click "Format..." button
- Go to "Fill" tab
- Pick a color
- Click OK
- Click OK

**Change font color for dates not in selected month**

- Repeat 1-5 steps above
- =MONTH(B5)<>MONTH($B$6)
- Click "Format..." button
- Go to "Font" tab
- Pick a color
- Click OK
- Click OK

- Repeat 1-5 steps above
- =OR(B5=INT(INDEX(Data,0,1)))
- Click "Format..." button
- Go to "Fill" tab
- Pick a color
- Click OK
- Click OK

### Formulas

- Select sheet "Data"
- Go to tab "Formulas"
- Click "Name Manager" button
- Click "New..."

- Name: Data
- Refers to:

=OFFSET(Data!$A$2,,,COUNTA(Data!$A$1:$A$1000)-1,3) - Click OK!
- Click Close

- Select sheet "Calendar"
- Select cell B13

- Array formula:
=IFERROR(INT(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(A1)), COLUMN(A1))), "")
- How to create an array formula
- Select cell D13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(B1)), COLUMN(A1)), "")
- Select cell F13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(C1)), COLUMN(B1)), "")
- Select cell H13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(D1)), COLUMN(C1)), "")

**How to create an array formula**

### Visual basic for applications

- Right click on Calendar sheet
- Click "View code"
- Copy vba code
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B5:H10")) Is Nothing Then Range("G2") = Target.Value End If End Sub

- Paste to code module

**Hide value in cell G2**

- Select cell G2
- Press Ctrl + 1
- Go to tab "Number"
- Select category: Custom
- Type ;;;
- Click OK!

### Download excel *.xlsm file

### Functions in this post:

**INDEX(array, row_num, [column_num])**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

**MATCH(lookup_value, lookup_array, [match_type])**

Returns the relative position of an item in an array that matches a specified value in a specific order

**SMALL(array, k)**

Returns the k-th smallest value in a data set.

**ROW(reference)**

Returns the row number of a reference.

**IFERROR(value, value_if_error)**

Returns value_if_error if expression is an error and the value of expression itself otherwise.

### 18 Responses to “Excel calendar (vba)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

In the "Calculating dates (formula)" section, Step #2... you misspelled "January" as "Janauary" in the latter part of the formula.

Thanks!

[...] number formats, and much more. Home > Excel > Automate > Use a calendar to filter a table← Previous post - Use a calendar to filter a tableFiled in Automate, Drop down lists, Excel, Search/Lookup on Sep.10, [...]

[...] How to create an Excel calendar with VBA [Get Digital Help] [...]

hi,i am new to this blog.

but i hope i can get some help regarding a pop up calendar.

i found a pop up calendar for excel 2007,but when i try to use it in Excel 2010, the calendar does not appear. and i think it is related with the format behind.can you help me?thank you for any help and sorry for my poor English.

auni,

Do you have a link to the pop up calendar?

http://www.fontstuff.com/vba/vbatut07.htm

Hi, I would like to use this example with my data set however I'd like to visually show the amount of events per date to understand when are we the busiest, slowest, etc. and be able to forecast using this data. Ideally I would like some sort of data bar or color change indicating the level for each date (Jan 1 has 10 items while Jan 2 has 3 and I can visually see that in each cell instead of seeing numbers or a solid color for each cell (here yellow and blue).

Also, would it be possible to have Excel know the days of the month as in first 7 days of the month, last day of the month, every Monday of the month. I'd eventually like to get formulas set up to tell me on average how busy we are during these periods.

David,

Here is a basic example. It looks like a heat map.

Highlight dates VBA

Download excel *.xlsm file

Calendar-David.xlsm

Contact me if you want more features.

[...] David asks: [...]

Hi there, I have downloaded the above calendar - and want to add another filter so I can categorize the events/meetings in the calendar, e.g. Reg for regulatory, Ben for benefits - and so when you click on these it will filter for that type - is this possible to do? Appreciate your help with this.

Thanks

Rosie,

Yes, it is possible. Upload a file and I´ll see what I can do.

This looks great. I would like to use your calendar for a spreadsheet I am currently using. How could I just change where the event section refers to so it will automatically fill in the dates and times I need.

To give you context, I have a spreadsheet with many columns containing information. In these columns I have multiple dates/times for some rows. I want to create a calendar that references my spreadsheet with its events. I would like to keep all the information so that I can look at it still in the detailed view of each day (with all the information from the original row and also be able to reference the spreadsheet for multiple times for the same row but on different days).

How would I change the where the events are referenced to?

How could I add another option for dates so that I can add an extra column of values (I have the original dates column in the data sheet linked to another workbook?

[…] I got this codes from: http://www.get-digital-help.com/2012/09/05/excel-calendar-vba/ […]

Dear Sir

Please I would like to know how to highlight 2nd and fourth Saturday and all sunday as weekend. Can you please suggest vba code or formula.

advance thanks.

Yours faithfully

buvanamali

[…] How to create an Excel calendar with VBA [Get Digital Help] […]

Hi,

Thank you so much for the helpful template. This is awesome! However, when I choose January, it gives me all N/A values, can you please let me know what's going on? Thanks