Author: Oscar Cronquist Article last updated on October 03, 2019

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The calendar sheet allows you to enter a year and use a drop down list to select a month.

A small VBA event code tracks which cell you have selected and shows the corresponding events accordingly.

How this workbook works

The animated image above shows how to enter data and how to select a given date. Today's date is highlighted yellow, days with one or more events are also highlighted, in this example blue.

Excel extracts data dynamically meaning the named range grows automatically when new data is entered, we don't need to change the formula cell references.

How I created the calendar

Table of Contents

  1. Calendar
    1. Drop down lists
    2. Headers
    3. Calculating dates (formula)
  2. Conditional formatting
    1. Today
    2. Dates not in selected month
    3. Events
  3. Formulas
    1. Dynamic named range
    2. Calendar sheet
    3. How to create an array formula
  4. Visual basic for applications
  5. Download excel *.xlsm file

Drop down lists

Select year

  1. Select cell B2
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Allow: List
  5. Source:2012,2013,2014,2015,2016
  6. Click OK

Select month

  1. Select cell E4
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Allow: List
  5. Source:January, February, March, April, May, June, July, August, September, October, November, December
  6. Click OK

Headers

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

Calculating dates (formula)

  1. Select cell B5
  2. 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
  3. Select cell B6
  4. Formula:
    =B5+1
  5. Copy cell B6 (Ctrl + c)
  6. Select cell range D5:H5
  7. Paste (Ctrl + v)
  8. Select cell B6
  9. Formula:
    =B5+7
  10. Copy cell range C5:H5
  11. Select cell range C6:H6
  12. Paste (Ctrl + v)
  13. Copy cell range B6:H6 (Ctrl + c)
  14. Select cell range B7:H10
  15. Paste (Ctrl + v)

Conditional formatting


Highlight Today

  1. Select cell range B5:H10
  2. Go to tab "Home"
  3. Click "Conditional formatting" button
  4. Click "New Rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Format values where this formula is true:
    =B5=TODAY()
  7. Click "Format..." button
  8. Go to "Fill" tab
  9. Pick a color
  10. Click OK
  11. Click OK


Change font color for dates not in selected month

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


Highlight days with events

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

Formulas


Dynamic named range

  1. Select sheet "Data"
  2. Go to tab "Formulas"
  3. Click "Name Manager" button
  4. Click "New..."
  5. Name: Data
  6. Refers to:
    =OFFSET(Data!$A$2,,,COUNTA(Data!$A$1:$A$1000)-1,3)
  7. Click OK!
  8. Click Close


Event formulas

  1. Select sheet "Calendar"
  2. Select cell B13
  3. Array formula:
    =IFERROR(INT(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(A1)), COLUMN(A1))), "")
  4. Enter this formula as an array formula, see these steps if you don't know how.
  5. Select cell D13
  6. Array formula:
    =IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(B1)), COLUMN(A1)), "")
  7. Select cell F13
  8. Array formula:
    =IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(C1)), COLUMN(B1)), "")
  9. Select cell H13
  10. 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

  1. Select a cell
  2. Click in formula bar
  3. Paste array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Visual basic for applications

  1. Right click on Calendar sheet
  2. Click "View code"
  3. Copy vba code below. 
    'Event code that is executed every time a cell is selected in worksheet Calendar
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Check if selected cell address is in cell range B5:H10
If Not Intersect(Target, Range("B5:H10")) Is Nothing Then

'Save date to cell G2
Range("G2") = Target.Value
End If

End Sub
  4. Paste to worksheet module.
Note, save your workbook with file extension *.xlsm in order to attach the code to the workbook.

Hide value in cell G2

  1. Select cell G2
  2. Press Ctrl + 1
  3. Go to tab "Number"
  4. Select category: Custom
  5. Type ;;;
  6. Click OK!

Calendar-event-finish

Download Excel file


Calendarv2.xlsm

Related articles

Calendar with scheduling [vba]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both [โ€ฆ]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two [โ€ฆ]

Show and hide a picture [VBA]

Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it [โ€ฆ]

List comments [VBA]

Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" [โ€ฆ]

Find the longest/smallest consecutive sequence of a value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show [โ€ฆ]

Calendar โ€“ monthly view

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe [โ€ฆ]