< Go Back

Data validation date in next 30 days

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))

Explanation

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.

Example

Figure 1: Data validation date in the next 30 days

In figure 1 above, the data validation applied to D4:D7 is:

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

The conditions test the input for two things:

  1. Greater than today – D4>TODAY() or
  2. Less than today + 30 – D4<=(TODAY()+30)

The two tests for data validation in the next 30 days.

Useful notes

  • Since dates are serial numbers in Excel, it is easy to simply add 30.
  • Excel features many integrated data validation rules for dates.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar