Excel Calendar Drop Down

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

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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.

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.

  • First, we must select the cell to create a drop-down list. We must click “Data Validation” under the “DATA” tab. It will bring below the “Data Validation” window. If you are a fan of a shortcut key, we can press the shortcut Excel key “ALT + A + V + V” to open the above “Data Validation” window instead of going through the above steps. We must choose what kind of validation we will create for the drop-down list. So click on the drop-down list of “Allow:” and select “Date.” Once the “Date” is chosen, we must enter “Start Date & End Date.” Assume we need to create data validation from 01st Jan 2020 to 31st Jan 2020, then enter the start and end date as shown below. Finally, click on “OK,” and we will exit the data validation window. However, we do not see any drop-down lists in the selected cell. But try entering any value here. When we tried entering the value as “hello,” it gave us the warning message, “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. It accepted the value when we inserted the date between the start and end date. So, like this, we can create data validation to restrict users from entering dates between specific ranges.

If you are a fan of a shortcut key, we can press the shortcut Excel key “ALT + A + V + V” to open the above “Data Validation” window instead of going through the above steps.

It accepted the value when we inserted the date between the start and end date. So, like this, we can create data validation to restrict users from entering dates between specific ranges.

  • We must create a start date and end date like the one below.

  • We need to select the above data range to name this date range”Date”read more.

  • Then, under the “FORMULAS” tab, click “Create from Selection.“

  • As a result, it will open the window below.

  • Since our names are in the selection area on the left side, we must check the “Left Column” box. Then, click on “OK.”As a result, it will create a named range.

  • Again, we will open the “Data Validation” window; this time, we do not manually enter dates. Then, we will select the box start date and press the “F3” key to bring the list of all the named ranges.

  • We must select the start date and end date, respectively. We will have named ranges instead of direct entries of dates.

We can only enter dates between the start and end dates in the selected cell. So, if we want to change start and end dates, change them, and according to the changes made, validation too will vary.

Example #3 – Create Dynamic Dates

Suppose you want the date ranges to be named automatically. In that case, we need to use 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, if we allow the user to enter dates between starting today and the end of this month, 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.

The TODAY function will keep on changing, and EOMONTH will adjust accordingly. Like this, we can create a calendar date drop-down list.

Things to Remember

  • The named range automatically takes the name from the selection based on the checkbox we tick.The drop-down selection will not be available, but we can only enter dates between provided dates.

This article is a guide to Calendar Drop Down in Excel. Here, we discuss how to create Excel calendar drop down list using data validation along with examples. You can learn more about Excel functions from the following articles: –

  • Edit Drop-Down List in ExcelCalendar Template in ExcelPower BI CalendarExcel Date Picker