Go Back

Excel DATA VALIDATION Using the TODAY and AND Functions

Read time: 25 minutes

Figure 1. Excel DATA VALIDATION Using the TODAY and AND Functions.

Let’s assume that we have to generate a DATA VALIDATION using Excel’s TODAY and AND Functions to permit entries of dates occurring within the next 30 days, we must utilize a custom formula syntax based on the AND and TODAY Functions in Excel.

Formula Syntax

=AND(logical1,[logical2], …)

This formula syntax returns as a result TRUE or FALSE. Which simply means that formula will return FALSE if all conditions in the formula are FALSE, and TRUE when all conditions in the formula are TRUE.

How to use Excel DATA VALIDATION Using the TODAY and AND Functions.

Let’s say we have to create a data entry validation for dates exceeding 30 days from today in Excel,, we are going to get this done by following 3 simple steps;

  1. In our worksheet example below, we have a collection of dates scheduled for interviews;

Figure 2. Excel DATA VALIDATION Using the TODAY and AND Functions.

Our purpose here is to flag any date entries that occur after a period of 30 days from the present date.

  1. The DATA VALIDATION formula that we will enter into cell C2 of our worksheet example is as follows;

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

Figure 3. Excel DATA VALIDATION Using the TODAY and AND Functions.

Excel returned the result of the Data Validation as TRUE because the date is within the 30 day period (assume that today is 27th of January 2019)

  1. Copy and paste the modified version of the Data Validation formula in cell C2 above, down into the other cells in the RESULT column for the next results.

Figure 4. Excel DATA VALIDATION Using the TODAY and AND Functions.

We can use the Excel AND function when we want more than one or both conditions to be met.

Figure 5. Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

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