Date Selections for HD Parameter Queries

I receive frequent requests for reports that require the user to provide a from and to date to the MS Query.  Ideally you would like to prevent the user from receiving an error message because a to date that is less than the from date is entered.  There are several way of accomplishing this and here is the one that I prefer.


Here's the layout of our sample workbook.

The todate list (column B) is built by entering =TODAY() in cell B13 and then entering =EOMONTH(B13,-1) in cell B12.  The formula in B12 is then copied and pasted in range B2:B11.  This populates the range B2:B12 with the last day of each month for the  previous 11 months.

The fromdate list (column A) is built by entering =DATE(YEAR(B2),MONTH(B2),1) in cell A2.  The formula is then copied  and pasted in the range B3:B13.  This populates the range A2:A13 with the first day of each month corresponding to the date in column B.

The Named Range "fromdate" is created by selecting the range A1:A13 and selecting  Formulas >  Create from Selection and clicking OK.

Select cell I2 and then Data > Data Validation and from the dropdown select List.  In the Source box enter =fromdate and click OK.  Cell I2 now has a drop down from which the user can select a date.  Format the cell with a date format.

Since the user should not be able to select a todate that is less than the fromdate it's necessary to determine where the todate list should begin based on the users selction for fromdate.  Enter =MATCH($I$2,$A$2:$A$13,0)-1 in cell E2.  The arguments for the MATCH formula are lookup_value, lookup_array, match_type.  The formula looks for the selected From Date (I2) in the list of From Dates (A2:A13) for an exact match (0) and returns the relative position of the cell where the match is found.  The -1 is used because the offset formual that will be used for the name range begins in row 2 instead of row 1. 

In cell E3 enter the formula =12-E2.  This will be used in the offset formula to determine how many cells to display. 

Select cell B2 then Formulas > Define Name and in the Name box enter todate.  In the Refers to: box enter =OFFSET(Sheet1!$B$2,Sheet1!$E$2,0,Sheet1!$E$3,1) then click OK.  The arguments for OFFSET formula are reference, rows, cols, height, width.  The reference is the cell address from which the offset will occur (B2).  Rows is the number of rows to offset from the reference cell, in this case it's the value in cell E2.  Columns is the number of columns to offset from the reference cell, in this case the value is 0 since we do not want a column offset.  Heighth  is the number of rows to be returned, in this caase we use the value in cell E3.  Width is the number of columns to be returned, in this case we only have the single column of data so we use 1.

Select cell I3 and then Data > Data Validation and from the dropdown select List.  In the Source box enter =todate and click OK.  Cell I3 now has a drop down from which the user can select a date that is always greater than or equal to the From Date.  Format the cell with a date format.

Cells K2 and K3 contain formulas to convert the dates to a format that can be used as parms for the MS Query .

  =SUM(YEAR(I2)*10000+MONTH(I2)*100+DAY(I2))

As always if you have any comments or questions you can enter them here or email me at the office.  Also, if you would like to have the sample workbook send me an email and I'll send it to you.