Comments on: Excel IF statement between two numbers or dates

To check if a given value is between two numbers, 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. Continue reading →

Comments page 2. Total comments: 85

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

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

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

    1. Hi! I hope you have studied the recommendations in the tutorial above. Use the logical functions AND and OR.

      =IF(OR(AND(A1>=DATEVALUE("15/4/2022"), A1<=DATEVALUE("15/5/2022")), AND(A1>=DATEVALUE("1/10/2022"), A1<=DATEVALUE("6/11/2022"))), A2, A3)

  3. 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!

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

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

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

  7. 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,??

  8. 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%

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

    1. You Sir are a genius! Many thanks Alexander!

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

  11. 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",)))

    1. sorry, that didn't make sense.

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

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

  12. 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?

  13. 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")

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

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

  16. 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"

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

  17. 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 ! :)

    1. Hi!
      Please read the above article carefully.
      Instead of AND(C5>MIN199,C5<MAX351) use AND(C5>199,C5<351)

  18. 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!

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

  19. 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!

  20. 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!

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)