*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:

*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:

*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:

### If between two numbers then

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(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.

*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:

*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:

*date*>=

*start_date*,

*date*<=

*end_date*), value_if_true, value_if_false)

Not including the boundary dates:

*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:

*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:

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

## 158 comments

I would like a hand with the following calculation if possible?

Low High Multiply Options Quantity Result

1 23 6 Option 1 15

24 42 17 Option 2 8

43 63 33

63 999 38

I have managed the following calculations so far, but would like additional IF's in the equation:

=IF(AND(SUM(Option 1 Quantity: Option 2 Quantity)>=Low 1,SUM(Option 1 Quantity: Option 2 Quantity)<=High 23),Option 1 Quantity*Multiply 6)

Options 1 and 2 will fluctuate, based on that I would like additional IF statements for increase Low High betweens.

Please let me know if you have questions about this.

Hi! If I understand your task correctly, the following tutorial should help: Nested IF in Excel – formula with multiple conditions.

Hello Sir Alexander Trifutov

please help me i want to classify Growth Number to this "GROWTH BELONGING"

GROWTH GROWTH BELONGING

96% 76% - 100%

66% 51% - 75%

44 26% - 50%

30 26% - 50%

10 1% - 25%

-20% -25% - 0%

-46% -50% - -26%

-66% -75% - -51%

-91% -100% - -76%

WHAT FUNCTION SO TO BE RESULTING THIS "GROWTH BELONGING" , THANKYOU SIR

Hi! Look for the example formulas here: Nested IF in Excel – formula with multiple conditions.

Hi, i need a formula that can give me the result (Region), if the value in code1 is my search, and range is between code2 and code3 and result is Region

code1 Code2 Code3 Region

2552 2492 2493 Lower Austria

2564 2500 2899 Lower Austria

2822 3000 3329 Lower Austria

2822 3331 3333 Lower Austria

2822 3340 3999 Lower Austria

3125 4000 4299 Lower Austria

3133 4300 4309 Lower Austria

3133 4392 4392 Lower Austria

3133 4430 4439 Lower Austria

3133 4441 4441 Lower Austria

3133 4482 4482 Lower Austria

3161 5000 5119 Salzburg

3161 5150 5169 Salzburg

3264 5200 5209 Salzburg

3264 5300 5309 Salzburg

3264 5320 5330 Salzburg

3321 5340 5340 Salzburg

3830 5342 5342 Salzburg

3841 5350 5359 Salzburg

thanks

Hello Ema!

We have a special tutorial that can help to solve your problem: Excel INDEX MATCH with multiple criteria - formula examples. The formula below will do the trick for you:

=INDEX(D2:D15,MATCH(1,(B2:B15<A2:A15)*(C2:C15>A2:A15),0))

Hello dear Alexander!

that was really quick reply. Thank you.

The formula works for many of the lines, but for some is giving wrong results.

i adjust it as =INDEX(BB:BB,MATCH(1,(AZ:AZAZ:AZ),0)), where BB is my region, AZ is code2; F4 is my search(code1); and BA is my code3.

but for example

if i search number 2000, should give me result Lower Austria, but result is Vienna

2000 2419 Lower Austria

1000 1999 Vienna

Can you please support me further?

Hi! Your written formula is not shown in full. However, I can assume that you will need to add a = to the formula. Also, I do not recommend using the entire column reference in the formula. This can slow down the calculations considerably.

=INDEX(D2:D15,MATCH(1,(B2:B15<=A2:A15)*(C2:C15>=A2:A15),0))

Hi Alexander,

I've changed the formula as you've mention above, but still facing issues.

I want to share a screen shot but i am not able to so i am sharing the table.

A B C D

Range 1 Range 2 Region Search code

2420 2429 Burgenland 6020

2473 2475 Burgenland 9132

2491 2491 Burgenland 9020

7000 7399 Burgenland 1190

7400 7419 Burgenland 1040

7422 7423 Burgenland 8010

7430 7479 Burgenland 4063

7500 7599 Burgenland 1110

8380 8389 Burgenland 2442

9000 9319 Carinthia 9512

9321 9322 Carinthia 2103

9400 9699 Carinthia 9500

9700 9779 Carinthia 2472

9781 9781 Carinthia 1090

9800 9899 Carinthia 3430

2000 2419 Lower Austria 1220

2430 2469 Lower Austria 5020

2471 2472 Lower Austria 1180

2480 2489 Lower Austria 4400

2490 2490 Lower Austria 8010

2492 2493 Lower Austria 4942

2500 2899 Lower Austria 1210

3000 3329 Lower Austria 1120

3331 3333 Lower Austria 1210

3340 3999 Lower Austria 4810

4000 4299 Lower Austria 1100

4300 4309 Lower Austria 1100

4392 4392 Lower Austria 8020

4430 4439 Lower Austria 4060

4441 4441 Lower Austria 2514

4482 4482 Lower Austria 1110

5000 5119 Salzburg 8010

5150 5169 Salzburg 8081

5200 5209 Salzburg 1050

5300 5309 Salzburg 9900

5320 5330 Salzburg 9853

5340 5340 Salzburg 2482

5342 5342 Salzburg 1100

5350 5359 Salzburg 1100

5400 5799 Salzburg 8054

7421 7421 Styria 1100

8000 8369 Styria 6130

8400 8999 Styria 8010

9323 9323 Styria 6020

9330 9379 Styria 1230

6000 6699 Tyrol 1210

9782 9782 Tyrol 4020

9900 9999 Tyrol 4020

3334 3335 Upper Austria 4061

4310 4389 Upper Austria 1210

4391 4391 Upper Austria 2540

4400 4429 Upper Austria 5230

4442 4443 Upper Austria 1020

4450 4479 Upper Austria 2522

4481 4481 Upper Austria 9020

4483 4444 Upper Austria 9181

4490 4499 Upper Austria 1120

4500 4999 Upper Austria 3100

5120 5149 Upper Austria 4600

5210 5289 Upper Austria 2380

5310 5319 Upper Austria 4040

5360 5369 Upper Austria 4550

1000 1999 Vienna 4030

6700 6999 Vorarlberg 1150

Hi! The data you are now using is different to the data you described in the first question. Try this formula for these data:

=INDEX($C$2:$C$65, MATCH(1,($A$2:$A$65<=D2)*($B$2:$B$65>=D2),0))

Hi Good day!

I need excel formula for below example.

BILLING DATE ,DISCONNECTIONDATE, SUSPENSIONDATE, REACTIVATIONDATE

A.FULLY ACTIVATED

B.NO REACTIVATION AFTER DISCONNECTION (before billing date)

C.NO REACTIVATION AFTER SUSPENSION (before billing date)

D.DISCONNECTED/SUSPENDED BUT REACTIVATED (reactivation occurred before billing date)

E.SUSPENDED BEFORE BD BUT REACTIVATED (AFTER Billing date)

F.DISCONNECTED or SUSPENDED (AFTER billing date)

G.ACTIVATED DURING CURRENT MONTH

Hi! Sorry, it's not quite clear what you are trying to achieve. Have you tried the methods described in this blog post? If you are not satisfied, please let me know and I will try to help you.

I am working with a large amount of financial data and trying to separate into Financial Statement type by GL#. I have entered the following formula:

IF((G2400000,G2500000, "Expenses")))

Balance sheet accounts GL# is less than 500000, Revenues are in the 400000 range, and expenses in the 500000. Excel is telling me this is an invalid formula. What am I doing wrong here?

Hi! Based on your description, it is hard to completely understand your task. I can assume that you have two conditions. So try using the IF AND formula. Look for the example formulas here: IF AND in Excel: nested formula, multiple statements, and more. If this does not help, explain the problem in detail.

Hi Alex,

What formula should I use if I want to check

If my Renewal End Date :4/27/2024 and close date is 4/4/2024 & I want to check if close date is less than or equal to renewal end date ?

Hi! Have you tried any of the methods described in this blog post? You can also use these instructions to compare two dates and display the desired message: Using IF function with dates. Simply compare the two cells and determine which one is larger.

How do i check if a cell is between two dates, and return one of three values.

I.E

Does A2 have a date between 01/01/2024 - 31/01/2024.

If yes, return Yes.

If no, return no.

If A2 has nothing in the cell, return as blank.

Hi! Have you tried the methods described in this blog post? The formula might look like this:

=IF(A2<>"",IF(AND(A2>D1, A2<D2), "Yes", "No"),"")

Read more: Nested IF in Excel – formula with multiple conditions.

Hi Alexander, thank you! I was trying that for hours, and yes i had read the blog post. I look at Excel formulas maybe one every 9/10 months, so i just don't hold the information very well!

It's really apprecaited.

Hi,

I would like to use IF function to calculate the age to a certain date and turn Yes the result is >=16 or No if it is <16. How can I dothat?

Hi! The following tutorial should help: How to calculate age in Excel from birthday. Calculate the age and use it in the IF formula as described in the article above.

=IF(OR(C3>=DATEVALUE("2/19/2024"), C3<=DATEVALUE("2/25/2024")),K3, 0)

I am trying to use a date range to select data on the same spread sheet in a different cell. If the reference cell meets the date range then the value in the "true" referenced cell, if not then enter a zero. I have tried the above formular however, it doesn't work. Any suggestions?

Hi! "2/19/2024" may not match your computer's date format. Also, note in the article above that you should use the AND operator, not the OR operator, for the date range. You can also try to set the date by using the DATE function. For example:

=IF(AND(C3>=DATE(2024,2,19), C3<=DATE(2024,2,25)),K3, 0)

I would very much appreciate if someone could explain to me how to make excel calculate the following:

Date Range using the if formula

My spreadsheet is comprised of names dates and then blank boxes for the 12 months of the year. I would like if excel could use the dates and place an x in each months box that corresponds to the date range. Example date range 1/12023 - 3/30/2021 it would place an x in January February and march and leave all the other boxes blank.

Hi! Your sample date range is text. Therefore, you cannot perform any calculations with it, including calculating months.

i want to assign a new value , if the number falls in between two numbers . there are ten such two number in 10 ten row

Hi! Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail.