Create a drop down calendar
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
- Select cell A1
- Select tab "Data" on the ribbon
- Press with left mouse button on "Data Validation" button
- Press with left mouse button on "Data Validation..."
- Select tab "Settings"
- Select List in "Allow:" drop down list
- Type in "Source:" window
2011, 2012, 2013, 2014, 2015
- Press with left mouse button on OK!
Create a drop down list for months
- Select cell B1
- Select tab "Data" on the ribbon
- Press with left mouse button on "Data Validation" button
- Press with left mouse button on "Data Validation..."
- Select tab "Settings"
- Select List in "Allow:" drop down list
- Type in "Source:" window
January, February, March, April, May, June, July, August, September, October, November, December
- Press with left mouse button on OK!
Recommended articles
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Setup Sheet2
- Select sheet2
- Select cell A1
- Type
=row()
in cell A1 and then press ENTER
- Copy cell A1 and paste down to cell A31
Create a named range
- Select tab "Formulas" on the ribbon
- Press with left mouse button on "Name Manager" button
- Press with left mouse button on "New..." button
- Type
days
in Name: window
- 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
- Press with left mouse button on Close button
Recommended articles
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
- Select cell C1
- Select tab "Data" on the ribbon
- Press with left mouse button on "Data Validation" button
- Press with left mouse button on "Data Validation..."
- Select tab "Settings"
- Select List in "Allow:" drop down list
- Type in "Source:" window:
=days
- Press with left mouse button on OK!
How to create a list of drop down calendars
- Copy cell A1:C1
- 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:
The conditional formatting formula:
How to apply conditional formatting to cell D4
- Select cell D4
- Go to tab "Home" on the ribbon
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "New Rule..."
- Press with mouse on "Use a formula to determine which cells to format"
- Type the formula above in "Format values where this formula is true:"
- Press with left mouse button on "Format" button.
- Press with left mouse button on tab "Font"
- Pick font color white.
- Press with left mouse button on OK
- 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:
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.
Calendar category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]
This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]
What's on this page How to use this Excel Calendar How to add events How I built this calendar Worksheet […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]
This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
6 Responses to “Create a drop down calendar”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
@Oscar,
Here is a shorter Name Range formula that appears to work the same as the one you posted...
=Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$31, DAY(DATE(Sheet1!$A$1, MONTH(Sheet1!$B$1&1)+1, 0)))
As a side note, you can change the $A$30000 reference in your posted Named Range formula to $A$31 and it will work just as well.
Rick Rothstein (MVP - Excel),
Thanks!!
i can't understand this6
=Sheet2!$A$1:29 ---->> not working
29=INDEX(Sheet2!$A$1:$A$30000,DAY(DATE(Sheet1!A1,MATCH(Sheet1!B1,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0)+1,1)-1))
Try this formula:
The formula creates a cell reference to sheet 2, column A. The drop down list is then populated with values from sheet2, column A.
Please ....
=Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$31,DAY(EOMONTH(DATE($A$1,MONTH(1&$B$1),1),0)))
This also works....