Author: Oscar Cronquist Article last updated on January 10, 2019

The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates the dates accordingly.

There is a more advanced version here: Calendar – monthly view that lets you add events and more.

How the template works

Select a month and year. The cells (C2 and E2) are drop down lists. The calendar is instantly updated with dates. This makes it quick and easy to print months. You may have to adjust print area.

How I created the template

Adjust cell sizes

My calendar has seven columns and six rows filled with dates.

  1. Select the first seven columns
  2. Adjust width, I am using 128 px

  1. Select six rows
  2. Adjust height (90 px)

Create drop down lists

  1. Select cell C2
  2. Create a drop down list (Data validation)
  3. Select List
  4. In source field, type: January, February, March, April, May, June, July, August, September, October, November, December

Repeat with cell E2, in source field, type; 2011, 2012, 2013, 2014, 2015

Calculate start date

I created a second sheet "Calculation".

Formula in C4:

=DATE(Sheet1!E2, MATCH(Sheet1!C2, months, 0), 1)-WEEKDAY(DATE(Sheet1!E2, MATCH(Sheet1!C2, months, 0), 1), 2) + Enter

Cell range A1:A12 contains: January, February, March, April, May, June, July, August, September, October, November, December

Calendar formulas

  1. Select sheet1.
  2. Select cell A4, type in formula window: =Calculation!C4 + ENTER
  3. Select cell B4, type in formula window: =A4 +1 +  ENTER.
  4. Select cell A5, type in formula window: =G4 +1 +  ENTER
  5. Copy cell A5 and paste it down as far as needed.
  6. Select cell B4 and paste it to cell range B4:G9

Format cells

  1. Select sheet1
  2. Select cell range A4:G9
  3. Press with left mouse button on "Top Align" button (Home tab, excel 2007)
  4. Press with left mouse button on "Align text left" button (Home tab, excel 2007)
  5. Press CTRL + 1
  6. Press with left mouse button on "Number" tab
  7. Press with left mouse button on Category: Custom
  8. Type: D
  9. Press with left mouse button on OK

Create named range

  1. Create named range, named "month"
  2. In Referes to: field, type: =DATE(YEAR(Calculation!$C$4), MONTH(Calculation!$C$4)+1, 1)

  1. Select cell range A1:A12 in sheet "Calculation"
  2. Type months in name box

Conditional formatting

  1. Select sheet1
  2. Select cell range A4:G9
  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. Type: =MONTH(A4)<>(MONTH(month))
  7. Press with left mouse button on "Format..." button
  8. Press with left mouse button on "Font" tab
  9. Select a color (grey)
  10. Press with left mouse button on OK!
  11. Press with left mouse button on OK!

Get excel calendar template

Get the Excel file


Monthly-calendar-template.xls

Week starts with monday