The tutorial shows how to use an Excel IF formula to see if a given number or date falls between two values.
To check if a given value is between two numeric values, you can use the AND function with two logical tests. To return your own values when both expressions evaluate to TRUE, nest AND inside the IF function. Detailed examples follow below.
Excel formula: if between two numbers
To test if a given number is between two numbers that you specify, use the AND function with two logical tests:
- Use the greater then (>) operator to check if the value is higher than a smaller number.
- Use the less than (<) operator to check if the value is lower than a larger number.
The generic If between formula is:
AND(value > smaller_number, value < larger_number)
To include the boundary values, use the greater than or equal to (>=) and less than or equal to (<=) operators:
AND(value >= smaller_number, value <= larger_number)
For example, to see if a number in A2 falls between 10 and 20, not including the boundary values, the formula in B2, copied down, is:
=AND(A2>10, A2<20)
To check if A2 is between 10 and 20, including the threshold values, the formula in C2 takes this form:
=AND(A2>=10, A2<=20)
In both cases, the result is the Boolean value TRUE if the tested number is between 10 and 20, FALSE if it is not: In case you want to return a custom value if a number is between two values, then place the AND formula in the logical test of the IF function. For example, to return "Yes" if the number in A2 is between 10 and 20, "No" otherwise, use one of these IF statements: If between 10 and 20:
If between two numbers then
=IF(AND(A2>10, A2<20), "Yes", "No")
If between 10 and 20, including the boundaries:
=IF(AND(A2>=10, A2<=20), "Yes", "No")
Tip. Instead of hardcoding the threshold values in the formula, you can input them in individual cells, and refer to those cells like shown in the below example.
Suppose you have a set of values in column A and wish to know which of the values fall between the numbers in columns B and C in the same row. Assuming a smaller number is always in column B and a larger number is in column C, the task can be accomplished with this formula:
=IF(AND(A2>B2, A2<C2), "Yes", "No")
Including the boundaries:
=IF(AND(A2>=B2, A2<=C2), "Yes", "No")
And here is a variation of the If between statement that returns a value itself if TRUE, some text or an empty string if FALSE:
=IF(AND(A2>10, A2<20), A2, "Invalid")
Including the boundaries:
=IF(AND(A2>=10, A2<=20), A2, "Invalid")
If boundary values are in different columns
When smaller and larger numbers you are comparing against may appear in different columns (i.e. number 1 is not always smaller than number 2), use a slightly more complex version of the formula.
AND(value > MIN(num1, num2), value < MAX(num1, num2))
Here, we first test if the target value is higher than a smaller of the two numbers returned by the MIN function, and then check if it is lower than a larger of the two numbers returned by the MAX function.
To include the threshold numbers, adjust the logic as follows:
AND(value >= MIN(num1, num2), value <= MAX(num1, num2))
For example, to find out if a number in A2 falls between two numbers in B2 and C2, use one of these formulas:
Excluding boundaries:
=AND(A2>MIN(B2, C2), A2<MAX(B2, C2))
Including boundaries:
=AND(A2>=MIN(B2, C2), A2<=MAX(B2, C2))
To return your own values instead of TRUE and FALSE, use the following Excel IF statement between two numbers:
=IF(AND(A2>MIN(B2, C2), A2<MAX(B2, C2)), "Yes", "No")
Or
=IF(AND(A2>=MIN(B2, C2), A2<=MAX(B2, C2)), "Yes", "No")
Excel formula: if between two dates
The If between dates formula in Excel is essentially the same as If between numbers.
To check whether a given date is within a certain range, the generic formula is:
IF(AND(date >= start_date, date <= end_date), value_if_true, value_if_false)
Not including the boundary dates:
IF(AND(date > start_date, date < end_date), value_if_true, value_if_false)
However, there is a caveat: IF does recognize dates supplied directly to its arguments and regards them as text strings. For IF to recognize a date, it should be wrapped in the DATEVALUE function.
For example, to test if a date in A2 falls between 1-Jan-2022 and 31-Dec-2022 inclusive, you can use this formula:
=IF(AND(A2>=DATEVALUE("1/1/2022"), A2<=DATEVALUE("12/31/2022")), "Yes", "No")
In case, the start and end dates are in predefined cells, the formula becomes much simpler:
=IF(AND(A2>=$E$2, A2<=$E$3), "Yes", "No")
Where $E$2 is the start date and $E$3 is the end date. Please notice the use of absolute references to lock the cell addresses, so the formula won't break when copied to the below cells.
Tip. If each tested date should fall in its own range, and the boundary dates may be interchanged, then use the MIN and MAX functions to determine a smaller and larger date as explained in If boundary values are in different columns.
If date is within next N days
To test if a date is within the next n days of today's date, use the TODAY function to determine the start and end dates. Inside the AND statement, the first logical test checks if the target date is greater than today's date, while the second logical test checks if it is less than or equal to the current date plus n days:
IF(AND(date > TODAY(), date <= TODAY()+n), value_if_true, value_if_false)
For example, to test if a date in A2 occurs in the next 7 days, the formula is:
=IF(AND(A2>TODAY(), A2<=TODAY()+7), "Yes", "No")
If date is within last N days
To test if a given date is within the last n days of today's date, you again use IF together with the AND and TODAY functions. The first logical test of AND checks if a tested date is greater than or equal to today's date minus n days, and the second logical test checks if the date is less than today:
IF(AND(date >= TODAY()-n, date < TODAY()), value_if_true, value_if_false)
For example, to determine if a date in A2 occurred in the last 7 days, the formula is:
=IF(AND(A2>=TODAY()-7, A2<TODAY()), "Yes", "No")
Hopefully, our examples have helped you understand how to use the If between formula in Excel efficiently. I thank you for reading and hope to see you on our blog next week!
Practice workbook
Excel If between - formula examples (.xlsx file)
You may also be interested in
- SUMIF between two numbers in Excel and Google Sheets
- Sum if between two dates in Excel
- Excel IF function with multiple conditions
