Go Back

Excel DATA VALIDATION Using the TODAY and AND Functions

Read time: 25 minutes

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:

 

=AND(C3>TODAY(), C3<=TODAY()+30)

 

 

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:

=TODAY()

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:

=AND(logical1,[logical2], …)

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.

=AND(C3>TODAY(), C3<=TODAY()+30)

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I am trying to create a list of dates that automatically update based on the current month. I type in the month and it auto-fills the list with the days of that month. for example; October has 31 days but November only has 30 days. I only want the list of dates to show the days in the current month. for example 10/1/2-18 - 10/31/2018 then when November come around it shows only 11/1/2018 - 11/30/2018
Solved by S. H. in 12 mins
Hello, I am having an issue with a formula in excel. I have a list of dates and I need to count the number of dates between today and 30 days before today. I.e. How many times is a date occurring between now and 12 January 2018. I have been able to get the count of the dates that are past 1-29 days overdue, and 30+ days overdue but for some reason it won't work the other way. Logically, the following should work but doesn't and I have tried a number of different sums including -30, <, > etc =COUNTIF(F2:F211,"="&TODAY()+30)
Solved by M. D. in 14 mins
I have 3 rows Name, SSN, Date. I have dates from today to Dec 31 I need lets say 30 days highlight red, 60 days highlight yellow and 90 days highlighted green. How would I be able to code it so when the dates change from 90 days put to 60 days out and change to there respected color?
Solved by K. S. in 12 mins

Leave a Comment

avatar