Tap into the power of Excel’s data validation feature (free PDF)

Excel’s data validation feature offers an efficient way to perform a variety of tasks. This collection of techniques demonstrates several handy ways to put data validation to work.

From the ebook:

Excel’s data validation feature is underused because many users don’t realize how versatile it is, especially where dates are concerned. Dates seem to complicate things, but only in your head! This feature handles dates fine. Here are four ways to express dates using data validation: literal values, input values, a list, and formulas.

I’m using Excel 2016 (desktop) on a Windows 10 64-bit system. You can work with your own data or download the demonstration .xlsx and .xls files. Excel’s Table object isn’t supported in the menu version, so #3 won’t work in .xls files. The browser version supports existing validation controls, but you can’t create them or modify them in the browser.

The easiest way
Using literal dates is the simplest way to create a validation control that limits input to a range of dates. You just enter the first and last acceptable dates as follows:

1. Select the cell to which you’re adding the validation control. (For this example, I’ll use cell C4 and add a fill color so it’s easy to spot.)
2. Click the Data tab.
3. In the Data Tools group, click Data Validation.
4. In the resulting dialog, choose Date from the Allow dropdown. Doing so updates the dialog. The Data control defaults to Between, which is what we want, so don’t change that setting.
5. In the Start Date control, enter the first date in the period.
6. In the End Date control, enter the last date in the period (Figure A).
7. Click OK.

As you can see in Figure B, the control rejects a 2018 date. The setting is inclusive of both the start and end dates.

This is the simplest way to designate a range for a data validation control. There’s nothing wrong with taking the easy route. It’s easy to implement and easy to modify—simply select the cell, repeat the above steps, and change one or both dates. In addition, we used the Between operator to represent a range of dates—a time period. There are several operators; you should review them so you’ll know what’s available.