Explanation
Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values.
Because we want to allow only dates in the month of June 2016, we give AND with two logicals.
The first logical tests that input to C5 is greater than or equal to June 1, 2016:
C5>=DATE(2016,6,1)
The second logical tests that input to C5 is less than or equal to June 30, 2016:
C5<=DATE(2016,6,30)
If both conditions are TRUE, the AND function returns TRUE and input passes validation. If either condition is FALSE, AND returns FALSE and input fails data validation.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.
Related formulas
Data validation allow numbers only
Data validation allow text only
Related functions
AND Function
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF...
DATE Function
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
