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. Click "Top Align" button (Home tab, excel 2007)
  4. Click "Align text left" button (Home tab, excel 2007)
  5. Press CTRL + 1
  6. Click "Number" tab
  7. Click Category: Custom
  8. Type: D
  9. Click 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. Click "Conditional formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Type: =MONTH(A4)<>(MONTH(month))
  7. Click "Format..." button
  8. Click "Font" tab
  9. Select a color (grey)
  10. Click OK!
  11. Click OK!

Download excel calendar template

Download Excel file


Monthly-calendar-template.xls

Week starts with monday

Download Excel file


Monthly-calendar-template-weekstart-mon.xls

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Monthly calendar template #2

I have created another monthly calendar template for you to download. Select a month and year in cells A1 and […]

Plot date ranges in a calendar part 2

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

Highlight events in a yearly calendar

This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]

Heat map yearly calendar

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]

Calendar – monthly view

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

Time sheet for work

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]