How to validate the entries in Microsoft Excel

In this article, we will learn how we can validate date entries in Microsoft Excel.

Data Validation list is used for making the error free data in Microsoft Excel. It is a technique by which we can keep a check on errors by providing the drop down list to users.

We have an option to restrict the values within the range through data validity. Now let’s take an example and understand how we can restrict the date value through data validation.

In this post, we will learn two types of date data validation, in which drop down list will provide the date list on the basis of available dates in source data.

In Excel, we have credit card data in which we have to enter the bill generation date and transaction date such that the bill generation should not be earlier than1^st Oct 2014 and not should exceed today’s date. Transaction date should not be greater than bill generation date.

First we will learn how to validate the date entry for bill generation date, follow below steps:-

• Select the range E2:E8.
• Go to Data Validation.
• Data Validation dialog box will appear.
• Allow drop down list > Date.
• From Data, select ‘between’.
• Start date > 1-Oct-2014 and End Date > =TODAY()

• Click on ok.

Note:-When we will enter the date less than 1-Oct-2014 and greater than today’s date, cell will accept the entry.

In this way, we can validate the date entry in the range of data.

Now we will restrict the entries in transaction date which should not be greater than bill generation date.

Follow below step:-

• Select the cell F2:F8
• Go to Data Validation.
• Data Validation dialog box will appear.
• Allow drop down list > Custom.
• Enter the mentioned formula =F2<E2

• Click on ok.

Note: - When we will enter the date greater than bill generation date, range will not accept the entry.

In these ways, we can validate the date entries in Microsoft Excel by using the data validation feature.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com