Excel Data Validation for Date Intervals

Excel Data Validation for Date Intervals. Ensuring data consistency and accuracy is crucial when working with dates in Excel. To prevent users from entering irregular dates that may hinder effective data analysis and increase work overhead, we can implement data validation for specified date intervals. In this article, we will learn how to enforce data validation for date intervals in Excel, allowing users to input dates only within a specific time range.

Example: Implementing Data Validation in Excel

Let’s consider a scenario where we have a specified date interval in cells D2 and E2. D2 contains the start date (2-Oct-19), and E2 contains the end date (9-Oct-19). In cell A2, users will enter a meeting date, and our goal is to ensure that the date entered falls within the specified time interval.

To achieve this, follow these steps:

• Select the cell(s) where you want to apply the validation.
• Go to the “Data” tab and click on “Data Validation.” You can also use the keyboard shortcut ALT > A > V > V.
• In the “Allow” dropdown, select “Custom.”
• In the “Formula” input box, enter the following formula:

=AND(A2>=$D$2, A2<=$E$2)

• In the “Error Alert” tab, write the following message in the “Error message” box: “Enter date within the specified date interval.”
• Click “OK” to apply the validation.

Now, when you try to input any date that falls outside the given date range in D2 and E2, Excel will reject the input, ensuring that only valid dates are accepted.

If you want to hardcode the dates and make the start date and end date fixed, use the following formula in the formula input box of data validation

=AND(A2>=DATE(2019,10,2), A2<=DATE(2019,10,9))

This will keep the start date and end date constant. You can then copy cell A2 and use “Paste Special” for data validation only to apply the same validation on other cells.

How it works?

The validation is based on the AND function, which checks if the user-entered date meets both criteria simultaneously.

AND(A2>=$D$2, A2<=$E$2): Excel evaluates the first criterion. If the value in cell A2 is greater than or equal to the value in D2, the formula returns TRUE; otherwise, it returns FALSE. Next, it checks the second criterion, whether the value in cell A2 is less than or equal to the value in E2. If it is, the formula returns TRUE; otherwise, it returns FALSE. If either of the criteria returns FALSE, the input is rejected.

In conclusion, data validation for date intervals in Excel ensures that only valid dates within the specified range are accepted. It enhances data consistency and accuracy while preventing users from entering inappropriate dates. Feel free to leave your questions or comments in the section below, and I’ll be glad to assist you with any Excel/VBA topics or clarifications related to this article.