How to Use Data Validation for a Date in the 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 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.

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.

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. 

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