Excel IF statement between two numbers or dates

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: Checking if a number is between 10 and 20

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") If between 10 and 20, return something, if not - return something else.

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") Excel IF between two numbers formula

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 between two numbers, return a value itself.

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") If between statement for interchanged boundary values

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") Check if a date is within a given range.

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. If between two dates formula

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") Checking if a date is within the next 7 days

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") Checking if a date is within the last 7 days

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)

Table of contents

158 comments

  1. If cell G8 is less than 200,000.00 then on cell H8, I'd like it to say NO LEVEL.
    If cell G8 falls between 200,000.00 and 350,000.00, then on cell H8, I'd like it to say SILVER.
    If cell G8 falls between 350,000.00 and 500,000.00, then on cell H8, I'd like it to say GOLD.
    If cell G8 falls between 625,000.00 and 1,000,000.00, then on cell H8, I'd like it to say PLATINUM.
    If cell G8 falls between 1,000,000.00 and 2,000,000.00, then on cell H8, I'd like it to say DIAMOND.
    Can all these arguments go in one cell?

  2. hi guys can you help me solve this,

    the condition is
    if the sum is 0-15 the percentage must be 50% in Cash and 50% in Installment,
    if the sum is 16-25 the percentage must be 40% in Cash and 60% in Installment ,
    if the sum is 26-40 the percentage must be 30% in cash and 70% in installment

    and if the installment reach the percentage the column shows "PASS"

  3. Here is my dilemma- I and in HEDIS/STARs Quality Improvement and I need to calculate an inverse percentage so the closer to 0 is the goal. then based on the inverse percentage I need to assign a quality score 5-1 based on the percent threshold.

    For example, If E11 is between 100%-15.01% return "1" if E11 is between 15%-36.01% return "2" etc.

    Inverse Calculation
    E10= Passing/Total
    E11=100-E10

    Who knows if I'm even doing that right!

  4. Hi I tried using the if function but am really struggling

    basically what i want to check is, if the date in f.e C9 is less than the current date today then it should say "expire" whereas if the date is C9 is higher than the date today it must say Active and also I want this to update itself so i dont have to go back everyday to update it

    Any assistance would be awesome.

  5. Hi I am struggling to correctly write a formula with multiple if Conditions. I need it to check column E3 to see if it says "Invoiced". If this is true, it looks at Column/Cell D3 to determine if the Due date of the invoice is between a certain range of dates. If this condition is also true, it will return the value listed in B3. If it is false, it will leave the cell blank. This formula is not working -

    =iferror(E3="Invoiced",(and(D3>=Datevalue("06/15/23"),D3<=Datevalue("06/30/23"),B3,"")))

    Thank you!

  6. Hello, I need help, please. I have a column that has dates (day, month, year) In a different column I need to display the value 2.8 if the dates are from June to October (any year) and 5.7 if the month are from November to May.

  7. Hi,

    I have a problem with a formula concerning dates...
    =IF(C9<$M$6;"Due";"Not Due")

    Where C9 is the due date of a invoice & M6 is the date where the breaking point if we will pay now or later. It works fine most of times but now I have a problem when the due date is in year 2022. Our breaking point (M6) is set to 6/30/2023 and with that all invoices with due date in 2022 (collum C) should show "Due". Some in 2022 does and some not, for example due date 12/16/2022, 10/31/2022 show "Due" correctly, but due dates 9/30/2022 & 8/31/2022 shows "not due" but it should show "Due". Can you see something wrong?

    P.s. We need to have the breaking point (M6) as a cell and not in the formula since this is a template and we only want to change the cell M6 and not the formula every time our breakpoint changes.

  8. How can I use Excel to return different scores in a fourth column depending on if a series of dates are all within one day of each other?

    Thank you!

    • Hi! All the information you need is in the article above. If you want a more detailed answer, give an example of the source data and the expected result.

  9. Hi,
    I'm trying to use the IFS function to calculate the corresponding date the following calendar month i.e. 7/7/2023 will return 7/8/2023, but 31, and if the following calendar month has less days the end of the month will be returned i.e. 31/1/2024 will return 29/02/2024.

    The IFS works great when only asked to consider the days of the month which extend over the end of the next month (the end of Jan and all the months with 31 says - except July and December). However, to include all the dates where 30 days need to be added individually (if the false value adds 31 days) and I'm running out of characters to be able to entre my formula.

    Am I making thinks difficult for myself or is there a simple method which I have not been able to find?

  10. =IF(AND(F138=250,F129=24),IF(AND(F138>=300,F129=32),IF(AND(F138>150,F138<250),18,0),0)))

    Sorry this is the formula I am trying to resolve.

  11. Good morning,
    i am try to make this formula correct Im just not sure if i have the correct order It answers " false" every time I try and alter.

    =IF(AND(F138=250,F129=24),IF(AND(F138>=300,F129=32),IF(AND(F138>150,F138<250),18,0),0)))

    can someone help please.

    Thanks in advance

    • Hi! I don't know what result you want to get. Your formula contains logical contradictions. If the first condition F138=250 is true, then after that the second condition F138>=300 can never be true. And if the condition F129=24 is true, then it can never be F129=32. Therefore the formula will always return FALSE.

  12. Hello,

    Would it be possible to create a formula that could accomplish something in this sense?

    - Check if the date shown in A1 is between Apr 15th - May 15th or Oct 1st - Nov 6th.
    - If so, highlight A2
    - If not, highlight A3

    Thank you for your help!

    Daniel

  13. My apologies, my comment below reflects a typo, it was meant to say C2 not C3. :( I hope my information was clear and concise.

  14. Hi,
    Thank you for your comment. Can you help me try to generate a formula I cannot seem to get it to work. Basically, I need a formula that can help me calculate if the value in B2 is within 21 - 35 days (3 - 5 weeks) from the date entered in A2 and reflect a "yes" or "no" in C3. If the date in B2 is less than 21 days or over 35 days, then the value is out of range.

    Within Range example:
    A2 =15-Jan-2023
    B2 = 15-Feb-2023
    C3 = Yes (within range)

    Out of Range example:
    A2 = 15-Jan-2023
    B2 = 30-Jan-2023
    C3 = No (Out of Range)

    Thanks in advance for your help with this!

  15. Hello,

    I'm just writing to say a huuuuuge thank you. This is exactly what I needed for my bachelor project. :)

  16. Hi, can you help me on how to automatically insert a date when a specific task is complete?

    For example:
    If the person has already submitted all requirements total of 33 = the date automatically insert once 33 has already reach.

  17. Hello!

    I need your help in regards to if you think I will be able to create a formula that is similar to the date format above but slightly different. I am using data from an English soccer league as part of a regression. I want to create a tab for fixture congestion, so for example how many times the team has played in the last 7 days.
    My data is set out as the following (I have not included any other irrelavant variables)
    Home Team Away Team Date
    AFC Bournemouth West Bromwich Albion 06/08/2021
    AFC Bournemouth Blackpool 21/08/2021
    AFC Bournemouth Barnsley 11/09/2021
    AFC Bournemouth Queens Park Rangers 14/09/2021
    AFC Bournemouth Luton Town 25/09/2021
    AFC Bournemouth Sheffield United 02/10/2021
    AFC Bournemouth Huddersfield Town 23/10/2021
    AFC Bournemouth Preston North End 03/11/2021
    AFC Bournemouth Swansea City 06/11/2021
    and so on... but it also includes data for the team when they go away...

    Home Team Away Team date
    Nottingham Forest AFC Bournemouth 14/08/2021
    Birmingham AFC Bournemouth 18/08/2021
    Hull City AFC Bournemouth 28/08/2021
    Cardiff City AFC Bournemouth 18/09/2021
    Peterborough United AFC Bournemouth 29/09/2021
    Bristol City AFC Bournemouth 16/10/2021
    Stoke City AFC Bournemouth 19/10/2021
    Reading AFC Bournemouth 30/10/2021
    and so on...
    I have this data for 24 different teams and altogether, there are over 500 entry points. I was wondering if doing this is possible at all and if it is, if the best way is seperating each team on a seperate sheet and inserting a unique IF function every time, or maybe a V look up?

    I hope I have explained this well enough.
    Thanks

  18. Hello!
    Hope you can help me out - how to calculate how many B values in a repeating range A values with a range of 1 row.
    For example: A B C B D A C B B D A A C B B D .... here there are 3 ranges of A values and I need your help how to know how many B values in each interval.
    Thank you so much!
    <3 <3 <3

  19. Hello, I wish to create an IF function that will provide me with variable results based on dates occurring before a date input directly into the formula. So, if C8, D8 and E8 are all less than 1/1/24, it give me one result, but if only 1 or 2 are before, I get different results based on how many dates are before the input date in the formula.

    I am currently trying to get the following formula to work:

    =IF(AND(C8<=31/12/22,D8<=31/12/22,E8<=31/12/22),"Answer 1"),IF(AND(C8<=31/12/22,D8=31/12/22),"Answer 2") etc...

    Any help would greatly appreciated. Really struggling with this.

  20. How do I place a formula to give 150 as result if gross salary is between kshs 0-6000, and formula to give 300 as result if gross salary is between ksh 6001- 8000, and the formula to give 400 as result if gross salary is between kshs 8001- 12000,??

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)