Data Validation to Reject Invalid Dates

Written by Amey Dabholkar

December 13, 2015

Hate Invalid Data Entry ? Then you need to check this out to prevent incorrect data to be entered in Excel. Here where the data Validation comes into existence. In this example, we are going to restrict data entry for Next 7 days. i.e I don’t want a user to enter data beyond the limit I prescribe. Check it out

Download the Exercise File Associated with this Tutorial

Step 1: Select some cells where you want to apply restriction of dates. In this case, it is cell A2 to Cell A5 in excel it is referred as A2:A5 

Step 2: Go to the Data Tab and select Data Validation. Check out the image below

Step 3: Once you click the Data Validation then onto the settings tab under validation criteria >> Apply select Date and just tick the Ignore blank. Under Data select between as we want user only to input between the dates specified by us.

Step 4: I have referenced Start Date to cell D2 and End Date to D3. On to the start date, you could add any date. In this case, i don’t want the user to enter the date previous to today’s date and also I don’t want users to enter a date beyond 7 days from now. Hence we will use =TODAY() function in Excel for the cell D2 and TODAY()+5 in Cell D3

Cell Formula
Cell 2 / 1 =TODAY()+5

Add an additional Message for the user not to make mistake with the data. It’s called as Input message. It is triggered when the user selects that cell. Check out how to do it :- While you are in the settings of Data Validation go to Input Message and then type the message.

Look at how it looks once you click the Ok Button. Check it out

Liked this trick ?
Share this with your Friends.



Submit a Comment

Your email address will not be published. Required fields are marked *

Pin It on Pinterest

Share This