Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula in case you want or need more control and flexibility.
To allow a...
To allow a user to enter only uppercase TEXT, you can use data validation with a custom formula based on the UPPER, EXACT, and AND functions.
In the example shown, the data validation applied to C5:C7 is:
=AND(EXACT...
To highlight the 3 smallest values that meet specific criteria, you can use an array formula based on the AND and SMALL functions. In the example shown, the formula used for conditional formatting is:
=AND($B5=$E$5,$...
To build a Gantt chart, you can use Conditional Formatting with a formula based on the AND function. In the example shown, the formula applied to D5 is:
=AND(D$4>=$B5,D$4<=$C5)
How this formula works
The trick...
You can combine logical statements with the OR and AND functions inside the IF function.
If color is red or green and quantity is greater than 10
In the example shown, we simply want to "mark" or "flag" records where...
To check if a date is within the last n days of today's date, you can use a formula based on the TODAY and AND functions. In the example shown, we are checking for dates in the last 7 days. The formula in D5, copied...
To highlight rows and columns associated with an approximate match, you can use conditional formatting with a formula based on the LOOKUP function together with with a logical function like OR or AND. In the example...
To test if a numeric value falls between two numbers, you can use the AND function with two logical tests. In the example shown, the formula in E5 is:
=AND(D5>MIN(B5,C5),D5<MAX(B5,C5))
How this formula works...
To confirm two ranges of the same size contain the same values, you can use a simple array formula based on the AND function. In the example shown, the formula in C9 is:
{=AND(B5:D12=F5:H12)}
Note: this is an array...
If you want to use conditional formatting to highlight cells that are NOT between two values (a lower and upper limit), you can use a simple formula that returns TRUE when a value meets that condition. For example, if...
To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function.
In the example shown, the formula in C5 is:
{=AND(...
To evaluate several options with a nested IF statement, one approach is to use a separate IF per line to show the result of each set of logical tests. By adding line breaks after each IF function, the formula becomes...
To build a Gantt chart by week, you can use conditional formatting applied with a formula based on the AND function. In the example shown, the formula applied to D5 is:
=AND((D$5+6)>=$B6,D$5<=$C6)
When the...
One way to extract multiple matches in Excel is to use INDEX and MATCH with a helper column that flags matching data. This avoids the complexity of a more advanced array formula. In the example shown, the formula in H6...
If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result is TRUE...