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. Get excel *.xlsm file

Drop down lists

Select year

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

Select month

  1. Select cell E4
  2. Go to tab "Data"
  3. Press with left mouse button on "Data Validation" button
  4. Allow: List
  5. Source:January, February, March, April, May, June, July, August, September, October, November, December
  6. Press with left mouse button on 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. Press with left mouse button on "Conditional formatting" button
  4. Press with left mouse button on "New Rule..."
  5. Press with left mouse button on "Use a formula to determine which cells to format"
  6. Format values where this formula is true:
    =B5=TODAY()
  7. Press with left mouse button on "Format..." button
  8. Go to "Fill" tab
  9. Pick a color
  10. Press with left mouse button on OK
  11. Press with left mouse button on OK


Change font color for dates not in selected month

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


Highlight days with events

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

Formulas


Dynamic named range

  1. Select sheet "Data"
  2. Go to tab "Formulas"
  3. Press with left mouse button on "Name Manager" button
  4. Press with left mouse button on "New..."
  5. Name: Data
  6. Refers to:
    =OFFSET(Data!$A$2,,,COUNTA(Data!$A$1:$A$1000)-1,3)
  7. Press with left mouse button on OK!
  8. Press with left mouse button on 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. Press with left mouse button on in formula bar
  3. Paste array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Visual basic for applications

  1. Press with right mouse button on on Calendar sheet
  2. Press with left mouse button on "View code"
  3. Copy vba code below. 
    'Event code that is rund 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. Press with left mouse button on OK!

Calendar-event-finish

Get the Excel file


Calendarv2.xlsm