WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Calendar Drop Down in Excel

Calendar Drop Down in Excel

Excel Calendar Drop Down

Dealing with dates in excel is tricky and error-prone. Therefore to ensure its error-free usage, we can create a calendar drop-down in excel using data validation. In this guide, we discuss how to do this step by step.

Calendar Drop Down in Excel

Examples of Calendar Drop Down in Excel

You can download this Calendar Drop Down Excel Template here – Calendar Drop Down Excel Template

Example #1 – Dates Drop Down List

When we allow the user to enter the data, we may need them to enter only a specific set of dates, so this is where the drop-down list plays an important role. Creating a date drop-down list is simple but effective as well.

  • First, select the cell where we need to create a drop-down list.

Calendar Drop Down in Excel Example 1

  • Now click on Data Validation under the “DATA” tab.

Example 1.1

  • This will bring below the data validation window.

Calendar Drop Down in Excel Example 1.2

If you are a fan of a shortcut key instead of going through the above steps, you simply press the shortcut excel key ALT + A + V + V to open the above data validation window.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Now we need to choose what kind of validation we are going to create for the drop-down list, so click on the drop-down list of “Allow:” and choose “Date”.

Example 1.3

  • Once the “Date” is chosen, then we need to enter “Start Date & End Date”.

Calendar Drop Down in Excel Example 1.4

  • Assume we need to create dates validation from 01st Jan 2020 to 31st Jan 2020, then enter the start and end date as shown below. Click on “Ok”, and we will exit the data validation window.

Example 1.5

  • However, we do not see any of the drop-down lists in the selected cell.

Calendar Drop Down in Excel Example 1.6

  • But try entering any value here. When we tried entering the value as “hello” it has given us the warning message as “This value doesn’t match the data validation restrictions defined for this cell.“

Calendar Drop Down in Excel Example 1.7

  • Now enter the date between 01st Jan 2020 to 31st Jan 2020.

Calendar Drop Down in Excel Example 1.8

When we entered the date between the start & end date provided, it has accepted the value, so like this, we can create dates validation to restrict users to enter dates between specific ranges.

Example #2 – Create a Named Range for Start & End Date

In the above example, we have directly supplied the start and end date but imagine the scenario where we need to change the start and end date frequently then we need to create named ranges.

  • Create a start date and end date like the below.

Calendar Drop Down in Excel Example 2

  • To name this date range, select the above data range.

Example 2.1

  • Under the FORMULA tab click on “Create from selection”.

Calendar Drop Down in Excel Example 2.2

  • This will open the below window.

Example 2.3

  • Since our names in the selection area on the left side check the box of “Left Column”.Click on “ok” and named range will be created.

Calendar Drop Down in Excel Example 2.4

  • Now again open the data validation window and this time do not enter dates manually. Select the box start date and press the F3 key to bring the list of all the named ranges.

Calendar Drop Down in Excel Example 2.4.5

  • Select the start date & end date respectively, and we will have named ranges instead of direct entry of dates.

Example 2.5

In the selected cell, we can enter dates between the start date and end date only. If you want to change start and end dates, change them and according to the changes made validation too will change.

Example #3 – Create Dynamic Dates

Assume you want the date ranges to be named automatically then we need to use TODAY function to change the date automatically.

  • For example, assume if we need to allow the user to enter dates between starting from today and the end of this month, then we need to apply the TODAY function for the starting date.

Calendar Drop Down in Excel Example 3

  • The next end date applies the EOMONTH function to arrive month-end date.

Example 3.1

Now TODAY function will keep on changing, and accordingly, EOMONTH too will change. Like this, we can create a calendar dates drop-down list.

Things to Remember

  • Named range automatically takes the name from the selection based on the checkbox we tick.
  • Drop down selection won’t be available but we can enter dates between provided dates only.

Recommended Articles

This has been a guide to Calendar Drop Down in Excel. Here we discuss how to create it using data validation along with examples. You can learn more about excel functions from the following articles –

  • Edit Drop-Down List in Excel
  • Calendar Template in Excel
  • Power BI Calendar
  • Excel Date Picker
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Calendar Drop Down Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More