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

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down lists and paste anywhere in the 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, however if you choose "January" and day 31 and then selects February which is possible. The problem is that there are not 31 days in February.

You can use conditional formatting to hide the day when that scenario happens, that will clearly inform the user that the date is not valid. I will show you later in this post how to set it up. Here is how I created the drop down lists:

Create a drop down list for years

  1. Select cell A1
  2. Select tab "Data" on the ribbon
  3. Press with left mouse button on "Data Validation" button
  4. Press with left mouse button on "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. Press with left mouse button on OK!

Create a drop down list for months

  1. Select cell B1
  2. Select tab "Data" on the ribbon
  3. Press with left mouse button on "Data Validation" button
  4. Press with left mouse button on "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. Press with left mouse button on OK!

Recommended articles

test
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

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. Press with left mouse button on "Name Manager" button
  3. Press with left mouse button on "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. Press with left mouse button on Close button

Recommended articles

test
Create a dynamic named range
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Create a drop down list for days

  1. Select cell C1
  2. Select tab "Data" on the ribbon
  3. Press with left mouse button on "Data Validation" button
  4. Press with left mouse button on "Data Validation..."
  5. Select tab "Settings"
  6. Select List in "Allow:" drop down list
  7. Type in "Source:" window:
    =days
  8. Press with left mouse button on 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.

Hide day if date is invalid

The image above shows what happens if an invalid date is selected, conditional formatting hides the day to alert the user that the date is invalid. The formula in cell J2 checks if the date is valid or not.

  • FALSE = Invalid
  • TRUE = Valid.

Formula in cell J2:

=MONTH(DATE(B4, MATCH(C4, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, 0), D4))=MATCH(C4, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, 0)

The conditional formatting formula:

=J2=FALSE

How to apply conditional formatting to cell D4

  1. Select cell D4
  2. Go to tab "Home" on the ribbon
  3. Press with left mouse button on "Conditional formatting" button
  4. Press with left mouse button on "New Rule..."
  5. Press with mouse on "Use a formula to determine which cells to format"
  6. Type the formula above in "Format values where this formula is true:"

  7. Press with left mouse button on "Format" button.
  8. Press with left mouse button on tab "Font"
  9. Pick font color white.
  10. Press with left mouse button on OK
  11. Press with left mouse button on OK

Example

The image above demonstrates how to filter a table using the date picker and an array formula.

Array formula in cell B7:

=INDEX(Sheet3!$B$3:$D$13, SMALL(IF(Sheet3!$B$3:$B$13=DATE($B$4, MATCH($C$4, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, 0), $D$4), MATCH(ROW(Sheet3!$B$3:$B$13), ROW(Sheet3!$B$3:$B$13)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell B7 and paste to cell range B7:D9.

Get the Excel file


drop-down-calendar.xlsx