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. Looking for a little help I want to make a cell auto fill a number The formula I have now is =IF(AND(C19>=150,C19<=149.99),"$1500","$1000")

    What I am looking for is if greater than 150 - "$1500", if between 120 &149.99 "$1000". if between 95 and 119.99 "$500" and if <94.99"$0"
    can anyone help please?

  2. Hi Any help on this...

    I need to calculate a number between the range of 2 numbers and I need to return 50% of the above value of whatever number has been input.

    i.e. Between 60,000 and 100,000 I need to return 50% of whatever number is input..... so if they input 75,000 the answer needs to be 7500 which is 50% of the value of 75000 minus 60000 = 15000 = ANSWER 7500

    Any ideas?

    • Hi! If you have two conditions, use an IF AND expression as described in these instructions: Excel IF: greater than AND less than. I also recommend that you read the first paragraph of the article above carefully. Your description of the calculations in the second paragraph contradicts the first paragraph. But I think you can write the calculation formula yourself.

  3. I've tried IF, AND, MATCH, XMATCH, and I can't seem to get my formula to work. In one sheet I have Move Out Date & Fiscal Year. In another sheet, I have the dates of the fiscal year (start & end) and the Fiscal Year name.

    Move Out Date Fiscal Year
    09/01/2011
    09/05/2011
    09/10/2011

    Start End Fiscal Year
    9/1/2011 8/31/2012 FY2012
    9/1/2012 8/31/2013 FY2013
    9/1/2013 8/31/2014 FY2014

    Depending on the date in the Move out Date cell, I would like for it to determine what the Fiscal Year is for that move out date. In other words, if column a, cell 1 is dated 9/1/2011, I want it to return "FY2012" into a column. Can anyone help me? Thank you.

  4. Hi! Need your help please to get the data I want using the compound "If" function to get the vacation leave balance based on the hiring data.
    Below are the details:

    A1: Hiring Date

    Conditions:
    * If hired between 1/1/2023 - 6/30/2023 = vacation leave should be 11 days
    * If hired between 7/1/2023 - 7/31/2023 = vacation leave should be 6 days
    * If hired between 8/1/2023 - 8/31/2023 = vacation leave should be 5 days
    * If hired between 9/1/2023 - 9/30/2023 = vacation leave should be 4 days
    * If hired between 10/1/2023 - 10/31/2023 = vacation leave should be 3 days
    * If hired between 11/1/2023 - 11/30/2023 = vacation leave should be 2 days
    * If hired between 12/1/2023 - 12/31/2023 = vacation leave should be 1 day

    Really appreciate if you can help me build a formula based on the above. Thanks in advance!

  5. if A1 gives current date and B1 says W then what is if formulae to show the actual date seen in A1 (not update it).

      • sorry it was not clear, i'll try again as your solution didn't give me the result i needed. thank you anyway
        A1 IS POPULATED WITH A DATE..... B1 IS EITHER D or W...... C1 needs to give the result.

        so what is the formula to be put into C! if B2 is W ....to replicate the date seen in A1. (it needs to be the same date that is there and not update it

        • Hi! Your description of the problem is not very clear. I will try to assume such a formula for cell C1:

          =IF(B2="w",A1,"")

          This will work if the date in A1 is written manually. If the date in A1 is entered using the TODAY function, use the method I recommended earlier.

          • thank you so much, that's worked a treat. Thank you for your patience too.
            sorry for the lack of clarity.

  6. Hi and thank you in advance,
    I'm trying to automate functional lab evaluations. So far, I've been able to use a nested conditional formatting:
    =IF(N7>=7.5,"Acute infection",IF(N7<=5,"Chronic infection"))

    The problem I'm having is that when a result is functionally in range it returns the word FALSE. I don't want anything to appear in a cell where the lab result is in the functional range (I have to build this because the functional range is much tighter than the reference range printed on the lab report--it just takes a lot of time to evaluate).

    How do I prevent the word FALSE and just show a blank cell. Otherwise my report looks extremely cluttered and is hard to go over with patients.

    Appreciate it!

  7. Hi there, looking to extract data during a one month period (eg. May 30 - June 30) across a 30 year period in one set of data. Is there a way to do this without having to repeat a formula 30 times?

    Thanks.

    • Hi! Use the DAY and MONTH functions to specify the desired dates. For example:

      =AND(DATE(2023,MONTH(A1),DAY(A1))>=DATE(2023,5,30),DATE(2023,MONTH(A1),DAY(A1))<=DATE(2023,6,30))

  8. Hello! I need help with a formula.

    Columns are:
    Column I - End Date: 5/31/2023
    Column M - Sign On Payout: 7/30/2023
    Column K - Amount: 1000

    I was hoping to write a formula that says if the end date is before or equal to the payout date then "N/A", if not the amount.

    =IF(AND(I5>=M5,I5<=M5),"n/a",K5) is what I wrote and its not working.

    thanks

      • I did this and its still not working. I even tried formatting the cells for the dates to be the same. Any advice?

        Thank you

  9. Hi,

    I need excel formula for below example.

    Sheet 1
    Date Part Code Qty Rate
    10-01-2023 ABC 10 25 Rate should be come through formula from "sheet 2" - First match "part code" & Date match with "sheet 2" between date
    12-01-2023 ABC 20 30
    11-01-2023 XYZ 5 12

    Sheet 2
    Part Code Valid from Valid To Rate
    ABC 01-01-2023 05-01-2023 20
    ABC 06-01-2023 10-01-2023 25
    ABC 11-01-2023 15-01-2023 30
    XYZ 01-01-2023 10-01-2023 10
    XYZ 10-01-2023 12-01-2023 12

    Thanks!

  10. Hi, I'm trying to pull through certain data from a sheet based on a name if the dates in column B on that sheet falls between a certain month only.

    =IF(('PC 156598'!B:B>=DATEVALUE("11/1/2023"),'PC 156598!'B:B<=DATEVALUE("11/30/2023")),VLOOKUP(C2,'PC 156598'!$A$2:$C$64,3,0),0)

    Excel is saying there's something wrong with the second tab reference 'PC 156598!' Have I nested a vlookup within the IF formula incorrectly?

    Please help! Thank you

    • Hi! I can assume that you wanted to use an IF formula with two conditions. However, you do not have an AND or OR operator to combine the conditions. For example,

      =IF(AND('PC 156598'!B:B>=DATEVALUE("11/1/2023"),'PC 156598!'B:B<=DATEVALUE("11/30/2023")),VLOOKUP(C2,'PC 156598'!$A$2:$C$64,3,0),0)

      For more information, please visit: Excel IF function with multiple conditions.

  11. Hi Alex,
    I am trying to determine if the current time (cell D3: =now() - formulated to XX:XX AM/PM) is between a shift start and a shift end time and return either "on-shift" or "off-shift". I am using these two formulas:
    1/ =IF(AND($D$3>D31, $D$3MIN(D23,E23),$D$3<MAX(D23,E23)), "On-shift","Off-shift") -- only gives "Off shift even when current time is between shift start and end time.

    Please help!

    Thanks in advance!

  12. I am getting "false" message for below formula. This is used to find out two days differance to find total number of days

    IF(($P$3-$O7)>=60,IF(($P$3-$O7)<90,$F7,0))

    Please help me to resolve this issue

  13. Trying to figure out how to produce this (not working)sheet has 2 tabs =IF column H matches column E (Names Match) on other tab (tab name) then check column D (Renewal Date) if less than or greater than date in column I5 (Jan-24) than produce column N Price or column O price

  14. Hi,

    I'm really struggling to find the way to do this, have tried multiple options in your guidance - but basically, I have two date columns and I want a series of different comments back - not simly late/on time - but four different options.

    Column 1 is the due date; column 2 is the completed date, where a date only appears when the task is completed. I want to return:

    if the completed date column is blank, but the due date has passed - return "open and late"
    If the completed date is blank, but the due date not yet passed - return "open and on time"
    If the completed date is entered but is later than the due date - return "closed late"
    If the completed date is earlier than the due date - return "Closed on time"

    So the two values option won't work IF(N1>K1, "completed late","on time") as it doesn't cover all the options and I tried nested variations but it seemed to think if the completed date was blank, it was on time, even when the due date was passed. It's the blank that I'm struggling - if it's blank, it depends on whether the due date is still live or has passsed as to what's returned. Many thanks

    • Apols - also the IF(X>Y, "completed late","on time") also doesn't always work with the blanks. So where the completed column is blank, often it brings back on time, even when the due date has actually passed.

    • Hi! Try to use the recommendations described in this article: Excel Nested IF statement: examples, best practices and alternatives. Check the empty cell using the ISBLANK function. If I got you right, the formula below will help you with your task:

      =IF(AND(ISBLANK(B1),TODAY()>A1),"open and late", IF(AND(ISBLANK(B1),TODAY()<A1),"open and on time", IF(AND(NOT(ISBLANK(B1)),B1>A1),"closed late", IF(AND(NOT(ISBLANK(B1)),B1<A1),"closed on time",""))))

      Hope this is what you need.

  15. Hi Alex ,
    thank you very much for your time , can you please help me with my formula please , all what I need to show is meet, not meet & (Blank if there is no date i the K columns) I did the Meet & not Meet formula but I just can't get the cell to be blank if there is no date entered , can you help me please , this is the formula I'm using =IF(K5>H5,"Meet",IF(K5<=I5,"Not Meet", I tried the IFBLANK ,"" , but nothing is working , what I need , when I enter date in K, column L will shoe me If meet and didn't meet (WORDs) the due date in (I) , but if there is no date in K I want L to be Blank,, can you help me please ..Thank you

    H I J K L
    Dyployment Date Lodgement due date Reminings days from today loedgment date Deadline were meet?
    1/09/2023 6/09/2023 13 4/09/2023 Meet

  16. Hi Alexander,
    I hope you can help me with the following problem I've encountered on excel. For explanation purposes, i'll use example cell R5 as a reference

    I'm creating a list (in rows) that uses one column (column Q) where I enter completion dates and in the next column (column R), excel auto-calculates the next due-dates by using the following formula R5=date(year(Q5)+2,month(Q5),day(Q5)) - if the due date is in 2 years, for example). This works perfectly and I could combine the calculated due date (say in cell R5) with conditional formatting to highlight when the future dates are coming close.

    As a follow on, I want Excel to auto-calculate how many of the rows in column R (future due-dates) are still within date and how many have expired to find out the overall compliance percentage. By adding in a third column and using an the formula =If(R5>=today(),"1","0") i wanted to assign numerical values to dates that have not yet come to pass and then have excel calculate the compliance based on the numerical value of 1, but in %. However, between the dates and numerical values, my Excel does not seem able to do this. Could you help me out on the formula please?
    Thank you so much in advance.

    KR, Stef

  17. I attempting to have Excel return a count of dates in a range (located on another sheet) that are within the last 30 days. I think I might be overcomplicating things though, and have only gotten the correct results using the following formula:
    =COUNTIFS(August2023!J23:J142, ">=7/4/2023")
    The problem is that I don't want to have to edit the formula in each cell every time I use the spreadsheet, so I would like to use the TODAY option instead of an actual date (7/4/2023 in my example above). When I have tried to do this, I have gotten errors. Is there a way to write this formula?

  18. Hi all,

    I need to schedule an outage and I want to choose a date that affects the least amount of schools while their testing is on. So, how can I see the best date? If choose the 14 August how many schools (and which if possible) have testing on that date? What if I choose 15 August, how many would that effect?

    I have over 6000 rows to look at so any formula tips would be much appreciated! :)

    School Name State Testing Startes Testing Finishes
    School 1 Victoria 01-Aug-23 12-Sep-23
    School 2 Queensland 10-Aug-23 31-Aug-23
    School 3 Victoria 15-Aug-23 30-Aug-23
    School 4 Queensland 28-Aug-23 31-Aug-23
    School 5 New South Wales 28-Aug-23 8-Sep-23

  19. hello! I have a spread sheet with lease expirations throughout the year. Lease expirations are in the L column. The words "NTC" are in the P Column. On another tab I would like to be able to calculate How many leases expiring between 12.01.2023 -12.31.2023 have the words NTC in the P column. Can you help?

  20. Hi seeking assistance, hope you can help me. I already set a date function:

    =IF(AND(E160>=DATEVALUE("12/1/2022"),E160<=DATEVALUE("12/31/23")),"YES","")

    then, i would like to compute it to a cell with a amount less 60% if the date function will reflect "YES"

    I hope it's clear. Thank you so much

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

      =IF(AND(E160>=DATEVALUE("12/1/2022"),E160<=DATEVALUE("12/31/23")),IF(A1*60%

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