This drop down calendar uses a "calculation" sheet and a named range. You can copy drop down lists and paste anywhere in workbook but they have to be in the same order and adjacent.

You can select a year and month and a formula calculates the right number of days in the last drop down list. See picture above.

Download excel calendar template
drop down calendar.xls
(Excel 97-2003 Workbook *.xls)

How I created the drop down calenders in excel 2007:

Create a drop down list for years

1. Select cell A1
2. Select tab "Data" on the ribbon
3. Click "Data Validation" button
4. Click "Data Validation..."
5. Select tab "Settings"
6. Select List in "Allow:" drop down list
7. Type in "Source:" window
   2011, 2012, 2013, 2014, 2015
8. Click OK!

Create a drop down list for months

1. Select cell B1
2. Select tab "Data" on the ribbon
3. Click "Data Validation" button
4. Click "Data Validation..."
5. Select tab "Settings"
6. Select List in "Allow:" drop down list
7. Type in "Source:" window
   January, February, March, April, May, June, July, August, September, October, November, December
8. Click OK!

Setup Sheet2

1. Select sheet2
2. Select cell A1
3. Type
   =row()

   in cell A1 and then press ENTER

4. Copy cell A1 and paste down to cell A31

Create a named range

1. Select tab "Formulas" on the ribbon
2. Click "Name Manager" button
3. Click "New..." button
4. Type
   days

   in Name: window

5. Type formula:
   =Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$31, DAY(DATE(Sheet1!A1, MATCH(Sheet1!B1, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, 0)+1, 1)-1))

   in "Refers to:" window

6. Click Close button

Create a drop down list for days

1. Select cell C1
2. Select tab "Data" on the ribbon
3. Click "Data Validation" button
4. Click "Data Validation..."
5. Select tab "Settings"
6. Select List in "Allow:" drop down list
7. Type in "Source:" window:
   =days
8. Click OK!

How to create a list of drop down calendars

1. Copy cell A1:C1
2. Paste down as far as needed.

The named range is dynamic, all drop down lists in each row is connected.

Download excel calendar template
drop down calendar.xls
(Excel 97-2003 Workbook *.xls)

Related posts:

Create a drop down list containing alphabetically sorted values in excel

Monthly calendar template #2 in excel

Excel calendar (vba)

Monthly calendar template in excel

Calendar with scheduling in excel 2007 (vba)