Excel Data Validation Fails

icondatavalidation Data validation is one of the best features in Excel. You can use it to create drop down lists, or limit what users can enter in a cell. Unfortunately, data validation isn’t perfect, or foolproof. Users can get around the limits, by pasting data into the cell, or by using the Clear All command in a data validation cell.
Someone sent me a question this week, asking how to trap input errors, despite these data validation failings:

  • Hello! I was wondering, to you have any idea how to error trap a date input? I will enter dates on a specific column (complete dates such as 11/05/2010) and error trap if the input has the year of 2011 and not 2010. I know data validation does that, but only if the cell is manually inputted. If the input in the cell is pasted, it does not do that anymore.

Add the Data Validation

In this example, an order form has two cells for dates – an Order Date, and a Delivery Date. To ensure that a date for the current year is entered, you can use formulas in the data validation, to set a minimum and a maximum date.
Start Date formula: =DATE(YEAR(TODAY()),1,1)
End Date formula: =DATE(YEAR(TODAY()),12,31)
datavalidationdate01

Check the Date

If you’re concerned that users might paste values into the cell, or clear the data validation, you can add a formula check, to ensure that a valid date was entered. In the order form, a date check formula is entered in column G, which can be hidden.
The formula in cell G9 is:
=OR(C9=””,YEAR(C9)=YEAR(TODAY()))
The formula is copied down to cell G10, and the result is FALSE, because the date in C10 is not in the current year.
datavalidationdate02

Block the Invoice Total

In the Invoice total cell, the formula result is “Invalid Date”, if either of the date check cells contains FALSE. If both dates are in the current year, the total sum is shown.
Here is the formula from the Invoice Total cell:
=IF(COUNTIF(G9:G10,FALSE),”Invalid Date”,SUM(E13:E17))
datavalidationdate03

Other Solutions

Instead of a formula check, there are other ways to ensure that users enter valid data. For example, you could use Excel VBA to check specific cells before printing, and cancel the printing if the entries aren’t valid.
Have you used other methods to ensure that users don’t ignore your data validation cells?
____________