In Excel, you can create custom validation rules based on your requirements. This post will guide you on how to test Data validation date in next 30 days using a combination of formula approach in Excel.
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))
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.
How the formula works
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.