Date Data Validation Drop-Down
In this post, we’ll create two data validation drop-down cells that provide the ability to select From and To dates based on the transaction dates stored in the source data. This will ensure that the date selections fall within a valid range of dates, that is, those months with data in the table. This technique is dynamic, so that as transactions are added or removed, it is easy to update the drop-down lists to reflect valid date choices based on the dates of the transactions.
Before we dig into the mechanics, let’s review our objective.
We export data from our accounting system on a regular basis, and use it to drive some reports. We’d like to provide the user with the ability to select a From and To date based on the transactions dates.
Consider the transaction export below.
Based on the data in the table, we want to provide two drop-downs that allow the user to select a From date and a To date, as follows.
The transactions in the table begin at 1/11/2015. Therefore, we want our From date drop-down to begin with 1/1/2015, and to include the first day of the month for all months with data, such as 2/1/2015 and 3/1/2015. If, next period, the data includes transactions for December 2014, then, we’d like the From date drop-down to include a start date option of 12/1/2014. Same logic for the To date, we want the drop-down to include the last day of the month for all months that have transactions, for example, 1/31/2015, 2/28/2015, and 3/31/2015.
Sound good? Alright, let’s proceed to the mechanics.
To summarize our game plan, we will store the data transactions in a table, create two helper columns in the table that auto-fill as new transactions are added, create an intermediate PivotTable to dynamically provide one row for each From and To date combination, use dynamic named references to grab the PivotTable values, and set up data validation to provide the in-cell drop down.
Let’s proceed one step at a time.
It is very important to store the data transactions in a table (Insert > Table). In this case, it will ensure that any new transactions pasted in future periods will automatically flow through the workbook since tables auto-expand to include new data rows. Plus, tables auto-fill calculated column formulas down…this will ensure that our helper columns automatically fill to any new data rows pasted into the table in the future.
We’ll create two new helper columns. We’ll be sure to place them to the right of the exported data so that in future periods, we can paste new transactions into the table as they come. Excel will auto-fill the formulas down through the new transactions.
The helper columns will provide the last day of the month and the first day of the month for each transaction date. We’ll name the new columns EOM and BOM for end of month and beginning of month.
The function that we will use to compute the EOM value is the EOMONTH function. The EOMONTH function is probably my favorite date function, and it returns the last day of the month. It even allows us to add or subtract months so that we can move ahead or back if needed. The formula for the EOM column is:
- [@Date] is the structured table reference for the current row date column
- 0 is the number of months to add or subtract from the transaction date; 0 since we want the last day of the transaction month
The results are shown below.
We’ll use a similar formula to compute the BOM column. The only twist is that since the EOMONTH function returns the last day of the month, we’ll need to add one day to its result to compute the first day of the next month. So, the formula for the BOM column follows:
- [@Date] is the date value
- -1 subtracts one month from from the transaction date
The results are shown below.
Since the data is stored in a table, Excel will automatically fill these helper columns down for any new data rows.
With our helper columns in place, it is time to create the PivotTable. The PivotTable will dynamically generate one row for each unique EOM and BOM. We insert the BOM field into the rows area, the EOM field into the rows area, change the report layout to tabular, remove subtotals and grand totals, remove Row Headers formatting, remove the +/- Buttons and Field Headers as shown below.
The PivotTable generates the From and To values that we need. Anytime new transactions are pasted into the table, we can update the PivotTable as needed by clicking the Refresh button (PivotTable Tools > Refresh). Also, we can sort the PivotTable in Ascending order by the BOM field to ensure new report rows appear in chronological order (activate the PivotTable and then use the Home > Sort command).
Now, we need to set up two named references that we can use in the data validation list source. The names need to dynamically adapt to the PivotTable’s dimensions and increase or decrease accordingly.
There are numerous ways to create a dynamic named range, and I’ll illustrate one such possibility. If you prefer another method, please share by posting a comment below.
For the From Date column, we’ll use the Name Manager (Formulas > Name Manager) to set up a new name, dd_datefrom. The name will include the range that spans from cell B7 on the Lists worksheet through the last cell in the PivotTable with the following formula:
- List!$B$7 is the first cell in the range
- INDEX(List!$B:$B,MATCH(MAX(List!$B:$B),List!$B:$B,0),1) determines the last cell in the PivotTable column by returning a cell reference in column B for the max PivotTable value. (Assuming the PivotTable is sorted in ascending order, the max PivotTable value will be the last PivotTable value.) We’ve explored the INDEX and MATCH functions numerous times before, please feel free to reference the related blog posts listed below for additional information.
The screenshot of the name is shown below for reference.
We set up a similar formula for the dd_dateto name, as follows.
With the dynamic names set up, all that remains is to set up the drop-down cells with the data validation feature.
We select the From Date input cell, and use Data Validation (Data > Data Validation) to allow a List equal to the name dd_datefrom, as shown below.
We select the Date To input cell, and set up data validation to allow a list equal to dd_dateto.
Now, the data validation drop down lists contain dates based on the dates in the transaction table as shown below.
As new transactions are added to the table, we simply refresh the intermediate PivotTable to retrieve updated drop-down choices. The dynamic named ranges incorporate them and feed them into the data validation input cells.
There are many ways to accomplish any given task in Excel, so, if you have an alternative or preferred method for any of the steps presented above, please share by posting a comment below…thanks!
- Sample File: DatesFromTo
- INDEX posts
- MATCH posts
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
we can also use OFFSET function to calculate the entire range of pivot rather using the above formula
I need some help to better understand how exactly you are creating and using [@DATE] where it is the Structured Table reference. How are you setting this up to begin with? @DATE is a function. So, I’m not understanding how, if a variable, you create the [@DATE] so it refers to the date in the column containing the dates (most left column of your Pivot Table Calendar).
Structured table references are automatically created by Excel when you insert a table using the Insert > Table command. You can then name the table using the TableTools > Table Name field, and the structured references are created automatically based on column names.
I understand table references are auto created. I don’t understand how you are creating and using @DATE in the context of :
WEEKNUM(@DATE). Since @DATE() is a built in function in Excel, I’m not grasping how you have used it in the Pivot Table Calendar post. You show how you use functions such as WeekNum() and WeekDayA() and Day() where @DATE is the argument.
The functions use [@Date], rather than @DATE, and the square brackets are used with structured table references. [@Date] is a structured table reference to the Date column in the table. The table name is omitted since that formula is written inside the same table. Hope this helps!
Thanks for the reply. I am still getting errors when attempting to create the @Date. Excel tells me, “the function is missing argument.” So, without actually seeing your code to create the @Date in the table it’s referencing where you don’t get the error, I’m going to have just reference the cell address containing the date (e.g., 1/1/2019) in the Date column.