Validate dates within next 7 days in data validation excel

In this article, we will learn How to Validate dates within the next 7 days using data validation in Excel 2016.
Data validation tool restricts the user to input data to ensure its accuracy and consistency in Excel. It helps in the consistency of the data. It guides the user to input the type of option which is necessary for your worksheet.

Validation of date entries for next 7 days is done with the use of  Excel functions. Following steps will be a guide, how to validate date entries.

Her we have a task that only date entries are allowed. So we need to check two conditions at the same time.

• The date cannot be prior to today.
• The date cannot exceed 7 days from today or the current date.

The use of functions will help us get through the situation.

• Data validation tool
• TODAY function
• AND function
• Operators

We will construct a formula out of it. First the date is checked that it doesn't lay before the current date. Then the date will be checked again from the date which is 7 days from current date. Last but not the least AND operator will check and returns TRUE if only the above two conditions matches. Then data validation tool checks if the formula returns TRUE it allows the date or else it shows error.

Use the formula

Explanation:

1. D4 >= TODAY( ) The first condition to check
2. D4 <= ( TODAY( ) + 7 ) The second condition to check
3. AND operator returns TRUE only if both conditions state TRUE or else it returns FALSE.

Let's get this through with this via using the formula using .

Here we have a list of products and its entry date needs to be filled manually. The product can arrive on the current date or within 7 days of the current date.

But we need to lock these entry_date input cells with some rules. Now we learn how to do it.

We will use the data validation attribute of excel. Follow the steps to do the same:

• Select the cells where data validation need to apply.
• Click Data > Data Validation option from the menu.

• The Data validation dailog box appears in front.

• In the Settings option, Select the Custom option from the Allow list.
• A formula box appears under the same option.
• Use the formula in the formula box.

Explanation:

• D4 >= TODAY( ) The first condition to check
• D4 <= ( TODAY( ) + 7 ) The second condition to check
• AND operator returns TRUE only if both conditions state TRUE or else it returns FALSE.

FIll the details as shown below and Click OK

Now when I enter the value as shown below. A default error generates.

As you can see an error is generated "This value doesn't match the data validation restrictions defined for this cell".

But now the problem is how the user know what restrictions are there and what type of value can the cell accept.

So for this, we will customize some changes as shown below.

In Input Message option, type the details under Title & Input Message box as shown above.

In Error alert option, type the details under Title & Error Message box as shown above.

Click Ok.

A message is displayed when the cell is selected And an error message is displayed as shown below.

Now fill the right answers, to complete the table.

To copy the data validation to the adjacent cells use the drag down or drag right option in excel.

Now our table is complete. Now its user friendly and informative as well.

Hope you understood how to Validate only uppercase in data validation in Excel. Explore more articles on Accounting here.  Please write your queries in the comment box below. We will help you.

Related Articles:

Data Validation in Excel : Restricts user to input value manually in Excel. 

Way to use Vlookup function in Data Validation : Allow values from the vlookup table in Excel.

Restrict Dates using Data Validation : Allow dates in the cell which lays within Excel date format.

How to give the error messages in Data Validation : Customize your data validation cell with error messages.

Create Drop Down Lists in Excel using Data Validation : Restricts user to input data manually. 

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel

Count cells which match either A or B