The most common type of Data Validation in Excel is a drop down list. In the example below I allow the user to select a year, then a month (using a drop down) and then enter a valid day in the month.

The structure we will use in shown below. You can download the example file using a button at the bottom of the post.

The data entry columns are B, C and D. Column A is used to simplify the process and column E is used to validate column D. The list of Months is in column G.

Column B is a data entry. It should be a whole number greater than 1900 (that’s when dates started in Excel). We can use a Data Validation to ensure that.

Shortcut

The quickest was to open the Data Validation dialog is to use a keyboard shortcut. Press these keys in sequence Alt A V V (don’t hold any of the keys down).

Year entry

Select cell B2 and press Alt A V V. In the Allow drop down select Whole number. In the Data drop down select greater than or equal to. Type 1900 in the Minimum box and click OK. See image below.

Month entry

Select cell C2. Press Alt A V V. In the Allow drop down select List. Click in the Source box that displays below and select the range G2 to G13. Click OK.

Day entry

Thanks to our wonderful calendar we need a flexible way to limit the day number in column D. There are a couple of ways to do this. I recommend using helper cells. The reason is that creating formulas in cells is much easier than creating them in the Data Validation dialog. Excel assists you when you create a formula in a cell. There isn’t much assistance in the Data Validation dialog.

We need a flexible way to identify the maximum day allowed for the selected year/month combination. We need year/month thanks to leap years and February.

In cell A2 enter the following formula

=IF(OR(B2="",C2=""),0,DAY(EOMONTH(DATE(B2,MONTH(1&C2),1),0)))

This formula

• Checks that we have a year and a month entered – if we don’t have both zero is displayed. The OR function returns TRUE if any of its logical tests are TRUE.
• Uses the MONTH function to extract the month number from the text in cell C2 (this is a hack I covered in this blog post)
• Uses the DATE function to build a date based on the year and month
• Uses the EOMONTH function to determine the last day of the month
• Uses the DAY function to extract the number of the last day on the month

In column A we now have the maximum value allowed for the year/month combination. We can refer to that cell in a Data Validation for cell E2.

Select cell E2, Press Alt A V V. In the Allow drop down select Whole number. Enter 1 in the Minimum box and click inside the Maximum box and then use the mouse to select cell A2 on the worksheet. Click OK.

Problem

There is a problem with this technique. After we have entered a valid year/month/day combination the user could go back and select a different year or month and that may mean the day is invalid. See example below.

Validating a validation

We need to add a check for the Day column. When performing validations I prefer to use logic calculations. These are calculations that return TRUE or FALSE. In this case if the validation is correct the formula will return TRUE. It will return FALSE if it isn’t valid.

We will use column E to validate column D. The formula for cell E2 is

=OR(D2="",D2<=A2)

If D2 is blank then TRUE will be displayed. If there is an entry in D2 then TRUE will be displayed if the value in D2 is less than or equal to the value in A2. See image below.

We can copy the range A2:E2 down the column.

One advantage with using one column to validate another is that you can count the number of FALSE entries to determine how many errors are there.

The following formula will count the number of FALSE cells in column E.

=COUNTIF(E:E,FALSE)

Download Example File

Adding conditional format

In the next blog post I will cover how to add conditional formats to the cells.