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. I need a formula to give me a fixed percentage based on two figures. Looking at the retirement fund lump sum withdrawal benefits tax income table (how much will the member be taxed on) I want to build a formula that once I put the person's fund value in one cell it will show me the percentage taxable in another cell.
    Table is as follow:
    Between 1 - 27 500 = 0%
    Between 27 501 - 726 000 = 18%
    Between 726 001 - 1 089 000 = 27%
    Between 1 089 001 and above = 36%

  2. Hi Guys,

    Firstly, thanks for the great content. It's helped me a lot (but I'm stuck!)

    I'm trying to compare 2 numbered lists.
    In List A, I have a list of numbers ranging from 6-11 digits in length, where each cell (A2, An) is a unique number.
    In List B, I have a different format, where column E & F are called Low Range & High Range respectively. It's purpose is to create a range where the number is sequential and doesn't break, if it breaks, it moves to the next range.

    For Example:
    The following numbers are depicted differently in both Lists below (200000, 200001, 200002, 200004, 200006, 200007, 200008, 200010)

    List A:
    Cell A2 = 200000
    Cell A3 = 200001
    Cell A4 = 200002
    Cell A5 = 200004
    Cell A6 = 200006
    Cell A7 = 200007
    Cell A8 = 200008
    Cell A9 = 200010

    List B:
    Cell E2 (Low Range) = 200000 - Cell F2 (High Range) = 200002 (i.e. includes 200001)
    Cell E3 (Low Range) = 200004 - Cell F3 (High Range) = 200004
    Cell E4 (Low Range) = 200006 - Cell F4 (High Range) = 200008 (i.e. includes 200007)
    Cell E5 (Low Range) = 200009 - Cell F5 (High Range) = 200009

    Problem I'm trying to solve:
    I'm trying to compare each cell (A2, An) in List A to each Low Range & High Range (Column E & F) combination. I have it working when I specify an individual cell in List A (A2) and compare it to E2 & F2 in List B using the following formula:

    =IF(AND(A2>=E1,A2<=F1),"YES","NO")

    What I can't figure out is, how can I compare each cell in List A to each Low Range & High Range pairing in List B?

    The expected output would be in List B, where B2-B8 = YES and B9 = NO (i.e. 200010 is not in any of the 4 ranges listed in List B)

    Let me know if you have any further questions and thanks again for looking into this for me!

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(SUM((Sheet1!A1>=Sheet2!$A$1:$A$4)*(Sheet1!A1<=Sheet2!$B$1:$B$4)),"yes","no")

      Copy it down along the column

      • You Sir are a genius! Many thanks Alexander!

  3. Trying to get a formulae to work which will populate a cell.

    If A2 is between 1-6 then in H2 will show Low, if A2 is between 7-12 then in H2 will show Medium, if A2 is between 13-16 then in H2 will show High,

    Cell A2 is a formulae arrived from other cells

    tried the If statements but don't appear to work

  4. Condition
    From- 1-Jan-23 (to) 10-Jan-23

    within A,B,C & D, i have to mention "YES" if "A" contains dates fulfilling the above condition or "No" . likewise for B,C & D. Kindly help.

    A 5-Jan-23 to 10-Feb-23
    B 11-Jan-23 to 10-Feb-23
    C 1-Jan-23 to 7-Jan-23
    D 11-Jan-23 to 10-Feb-23

  5. Hello, I'm trying to create a spreadsheet which returns overdue, active and imminent using the following formula. How can I express that when the date is 6 it returns OVERDUE. At the moment the imminent command is cancelling out the dates which should return ACTIVE.

    =IF(TODAY()>=H3+7,"OVERDUE",IF(TODAY()<H3+4,"IMMINENT",IF(TODAY()<H3+6,"ACTIVE",)))

    • sorry, that didn't make sense.

      I want it to display IMMINENT when TODAY is 6 from the date in H3.

      • oh boy, I'm sorry, the formula keeps changing when I post .
        I want it to display IMMINENT when TODAY is greater than 4 but less than 6 compared with the date in H3.

        • Hi!
          Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

          =IF(TODAY()>=H3+7,"OVERDUE",IF(AND(TODAY()>=H3+4,TODAY()<=H3+6),"IMMINENT",IF(TODAY()<H3+4,"ACTIVE",)))

    • Hi!
      I'm sorry, I'm afraid these pieces of info are not enough to give you a formula. Describe in detail the criteria for each of the three options and I will try to help

      • Thank You Alexander,

        What I'm trying to create is a list of duties which need to be completed every 7 days.

        In the H column, I want members of my team to type in the date that they last did the duty.

        If the date inputted is more than 7 days from TODAY, I want the column with the formula in to to display "OVERDUE"

        If the date is less than 7 but more than 2 days from TODAY, I what it to display "ACTIVE"

        If the date is 2 days before TODAY, I want it to display IMMINENT

        I have inherited the formula below but it never displays "IMMINENT". I am trying to amend this formula so that 2 days before it says OVERDUE, It flags up that the duty is imminent.

        =IF(TODAY()>=H3+7,"OVERDUE",IF(TODAY()<H3+6,"ACTIVE", "IMMINENT" ))

        • Sorry, I hadn't seen your 2.16 reply. I'm not sure my 2.30 message made sense. I've just trying the formula you suggested.

  6. Hi,

    I am trying to add comment Late or On time based on 2 dates: I have a column for due date and a column for actual date
    If due date and actual date are the same or actual date is earlier - then its on time
    If actual date is later than due date its late.
    How would I build this please?

  7. Hello,

    I want to input in a cell:

    if value in N3 is between 0 and 7, then "7 days", if between 0 and 14, then "14 days", if between 0 and 30, then "30 days", if between 0 and 60, then "60 days"

    I would appreciate your help

  8. I'm trying to create myself a time sheet - if I work less than 8 hours, i get a 30 min lunch break and if i work more than 8 hours i get a 45 min lunch break. So I've played around with a lot of different ways to do this, what i think isnt working is trying to get it to return a value in minutes. This is where I've got (but this doesnt work):
    =IFS(D2=timevalue"8:00",timevalue"00:45")

  9. i have an urgent column with yes or no, and a date column. I want to create a due date by adding either 2 days if yes and 7 days if no.

  10. I want to have a formula of startdate-31/12/22 but if startdate if lower than 01/01/22 then it will be just 12

  11. Hello, i have struggle to find a formula for this " if up to 70% then 80%,if between 50%-70% then 50% and if between 40%-50% then 40%. I need this all in one row.
    Thank you

  12. I am struggling to have multiple formula to create a target date for task completion.
    Column A has revision numbers of the document ranging from 0 to 4
    Column B has start date
    Column C should be target date (if Column A contains 0, the Column C should be +14 days, - formula is =IF(C4=1,WORKDAY.INTL(NB,7,16)). I would like to repeat the same formula with different C column values for at least 4 times.

    • Hi!
      Your formula does not match your question. I'm sorry, I'm afraid these pieces of info are not enough to give you a formula. Describe in detail what problem you have, and I will try to help you.

  13. I need to calculate commissions based on % of MSRP by product line.

    The % off of MSRP can vary between these cutoff amounts. Ie, the % MSRP could be 94% or 73%, the amounts don't always fall along these specific cutoff values but at these cutoff values, the commission rate changes:

    Column on worksheet % of MSRP % of MSRP % of MSRP
    Product Line = 100% >= 92.50% >= 75%
    column S 9% 6% 3%
    column T 9% 6% 3%
    column U 1% 1% 1%
    column V 4% 2% 1%

    MSRP is in column AD

    I need assistance with the formula to calculate the commission if a sale falls between these cutoffs.

    Thank you!

  14. hi if you can pls help
    A B C D E F G
    Week # Week # Vendor Amount Date
    Wed 12/8/2021 1 1 11/30/2021
    Wed 12/22/2021 2 1 11/30/2021
    Wed 1/5/2022 3 2 12/13/2021
    Wed 1/19/2022 4 2 12/16/2021
    Wed 2/2/2022 5 2 12/20/2021
    Wed 2/16/2022 6 3 12/23/2021
    Wed 3/2/2022 7 3 12/25/2021

    column "A" has a list of dates Column "B" has a list of the week number now when I enter a date in Column "G" column "D" should find the correct "week #" from column "B"

    • thank you for the quick reply
      the week number i have in column "B" is not the standard week number
      the date i have in column "G" is not in Column "A" since the date in column "G" is a date between 2 rows in column "A"
      any other sugetions?
      abe

  15. Hello,

    I am trying to display a text value if a number between 198.4 and 350.5 is displayed, but the formula is not working for me, I am entering this formula:

    =IF(AND(C5>MIN199,C5<MAX351),"FritsJurgens - System M+ - 70mm - Class D - Rectangular - Black","BLANK")

    I am struggling a LOT with this....

    Thank you ! :)

  16. Hello,
    I swear I've done this before but can't for the life of me recall...I have two tables:
    Table 1: Column C = a number I enter, Column D = a corresponding text based on table 2
    Table 2: Column A = a lower limit, Column B = an upper limit, Column C = text
    What I'm looking to do is IF Table 1, C = 10, so it is >= Table 2 Column A and <= Table 2 Column B, then Table 1 D = Column C in Table 2
    Hopefully that explains my conundrum.
    Thank you!

    • Hi Jeanette,

      In essence, you build a formula as explained in the "If between two numbers then" example, but instead of the hardcoded values, supply the corresponding references.

      Assuming your table 2 is on Sheet2 beginning in row 2, use the following formula for D2 in table 1:

      =IF(AND(C2>=Sheet2!A2, C2<=Sheet2!B2), Sheet2!C2, "")

      And then drag it down across as many rows as needed.

  17. Hi,

    I'm currently building a number sequence, using 5 columns and 50 rows. The numbers can't >9 if so they should +1 to the next row.

    for example you enter 51410 in the respective 5 columns and underneath it then starts counting up from 51410 to 51411 etc.

    I have been using the IF(AND function however eventually this sequence counts up from 9 going over the maximum value of 9.

    Example,

    A2 = 8 A3 =9
    B2 = IF(AND(F63>=9,E63>=9),"0",E63+1)
    B3 = IF(F63=9,"0",SUM(F63,1))

    is there any way around this.

  18. Hello,

    I would like to calculate how many instances of a word based on the date range formula - example below using November 2022 as range:

    Column C contains dates
    Column F contains word: high, medium or low
    Date range I am happy with & returns a value: =COUNTIFS($C:$C,">=01/11/2022",$C:$C,"<=30/11/2022")

    But I am struggling (without using VBA or variables) to bring back the number of instances of the word for that range?

    Hope it makes sense!

  19. How can I do a nested If statement with one of the the look up variables is a #. I have tried using wild cards, but end up with the same results.

    =IF((Z2-Y2)0,"Late", IF(OR(COUNTIF(Z2, "*"&"#"&"*")), "not received ", "")))

    The z2 and Y2 are date fields. The result is the same for the # it says #value!

  20. Hi guys,

    here is my dilemma, if this can be done in excel or not.
    I created a poker tracker sheet in excel to track my winnings on freerolls I play in, on various poker sites.
    Now,
    in Column D is my buy in, column E is won bounty and column H is price I won, and in column J is client name.
    Now, how can I calculate in column K for each separate site and track my winnings from each separate site in column K??
    Is it possible??
    I.e. IF client name is GG then total winnings are....
    IF client name is PS then total winnings are....

    I wish I could post a screenshot to explain better what I mean.

    Sincerely,
    Bonc

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