In Excel, you can create custom validation rules based on your requirements. This post will guide you on how to test Data validation for a date in next 30 days using a combination of formula approaches in Excel.
Excel data validation for a date in the next 30 days
The general formula on how to check for Data validation date in next 30 days is based on:
=AND(D4>TODAY(),D4<=(TODAY()+number of days))
Explanation of formula
To allow data validation of date in the next 30 days, you can employ the data validation formula based on the “AND” and “TODAY” functions when you want more flexibility and control.
The data validation rules are activated when a user changes or add a new cell value. The “TODAY” function returns the current date, which is recalculated on a regular basis.
The “AND” function, on its own, receives numerous logical expressions and returns TRUE when all the expressions are TRUE. This shows that the validation is successful. But if either of the expression returns FALSE, then the data validation fails.
Figure 1: Data validation date in the next 30 days
In figure 1 above, the data validation applied to D4:D7 is:
The conditions test the input for two things:
- Greater than today – D4>TODAY() or
- Less than today + 30 – D4<=(TODAY()+30)
The two tests for data validation in the next 30 days.
- Since dates are serial numbers in Excel, it is easy to simply add 30.
- Excel features many integrated data validation rules for dates.
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.