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.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Calendar Drop Down in Excel (wallstreetmojo.com)
Examples of Calendar Drop Down in Excel
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 listDrop-down ListA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more plays an important role. Creating a date drop-down list is simple but effective as well.
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.
- Now click on Data Validation under the “DATA” tab.
- This will bring below the data validation window.
- This will bring below the data validation window.
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. - 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”.
- Once the “Date” is chosen, then we need to enter “Start Date & End Date”.
- 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.
- However, we do not see any of the drop-down lists in the selected cell.
- 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”.
- Now enter the date between 01st Jan 2020 to 31st Jan 2020.
When we entered the date between the start and 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 rangesNamed RangesName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more.
- Create a start date and end date like the below.
- To name this date rangeDate RangeTo create a data range in Excel, click anywhere in the table and then go to table tools>design on the ribbon>convert to range. To do so, right-click the table and select table>convert to range.read more, select the above data range.
- Under the FORMULA tab click on “Create from selection”.
- This will open the below window.
- 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.
- 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.
- Select the start date & end date respectively, and we will have named ranges instead of direct entry of dates.
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 functionTODAY FunctionToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time.read more 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.
- The next end date applies the EOMONTH functionEOMONTH FunctionEOMONTH is a worksheet date function in excel which calculates the end of the month for the given date by adding a specified number of months to the arguments. This function takes two arguments: the date and another as integer, and the output is in the date format.read more to arrive month-end date.
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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers