If you want to learn how to create Excel DATA VALIDATION using the TODAY and AND Functions to allow entry of the date in the next 30 days, this article will give you the answer. DATA VALIDATION allows you to validate fields that are entered or changed by certain criteria. In this tutorial, we will cover data validation to allow entry only if the entered date is within the next 30 days.
Data validation using the TODAY and AND functions
To be able to do this, you will first need to understand how functions TODAY and AND work.
Now we will see the example of data validation to allow entry of the date in the next 30 days. In this example we will restrict data entrance and change in column Training date (cell range C3:C7):
Click on the data tab and under section Data Tools choose Data Validation. In tab Settings, part Allow, choose Custom. To restrict date entrance only to date in the next 30 days, enter the Formula:
Let’s first explain TODAY function as part of the formula. This function returns today’s date in the entered cell. This function has no arguments, so it’s syntax is very simple, you just need to type following in the cell:
In our data validation, we will use TODAY functions in two conditions. First one is that date in the cell is greater than today date (C3>TODAY() ) and the second one is that date in the cell is less than or equal to today date + 30 (which will return the date in exactly 30 days) – C3<=TODAY()+30.
As we want these two conditions both to be met, we will use AND function. If you want to check if several conditions are fulfilled at the same time, you should use AND function. The formula returns as a result Boolean TRUE or FALSE, and formula result will be TRUE only if all conditions in the formula are TRUE.
Formula syntax for AND function looks like:
In our example, logical 1 is C3>TODAY() – C3 cell is greater than today date, and logical2 is C3<=TODAY()+30 – C3 cell is less than or equal to date which is equal to today date plus 30 days.
We put a data validation condition only in cell C3. In order to expand it to column “Training date” you just need select relevant cell range and click Data Validation under Data tab, section Data Tools. You will be asked to expand Data Validation to the cell range.
If we try to enter the date more than 30 days from today (in our example, today is 21/09/2018, and we entered 23/11/2018), the error message will appear as in the example below:
You can change the text in the warning message going to Data Validation, Error Alert tab. In the box, Error message enter warning message and choose Style Stop. It is important to use style Stop since this style will allow you to enter data only with conditions defined in previous steps. If you choose style Information or Warning, you will be allowed to enter data that does not meet the defined condition.
Another option in data validation is to create a message that will appear when you select the cell which is under data validation. By doing this, you will inform the user that cell which he/she selected has data validation with description message. You can enter this message under Data Validation, Input message tab. In the box Input Message just enter the text that will be displayed to the user:
Now when user select cell that is under data validation, he/she will see the message about data validation:
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.