Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading →

Comments page 22. Total comments: 4823

  1. S.No Vehicle-Reg-No Rept-Date Rept: Time Entry Date In Time In Date Out Time Out
    1 GLT5739 02-Nov-20 19:20 03-Nov-20 21:50 04-Nov-20 23:40

    I want to make below report from my above excel sheet.
    1.In side the plant More than 12 hours Vehicles.
    2.Out side the plant More than 12 hours Vehicles.

  2. If the 1st 2 digit of the number in a cell is 11, i need to be rename as Import in another cell and if it is 10 , it has to be rename as Local , that is my requirement..please help....

  3. can you help me for this formula
    A Coolum contains 1) Acc repairs 2)running repairs B coolum contains value in c coolum contains Value if
    i want formula in d coolum if a running repairs value of b coolum if a coolum contains acc repairs then B-C

  4. Hi,

    Hope you are well, wonder if you can help me;

    I am trying to calculate; if CELL 1 has a date > than the date in CELL 2 then calculate days over (currently using =IF(F7>L7,F7-L7,0) but i want to add, if CELL 1 is blank then still calculate days over using todays date - CELL2.

    The current formula only works out the days over if a date has been inputted in CELL 1, but i need to use AND/OR condition but i cant get it work. (Date in cell 2 is another formula counting 1 year on from another date used)

    Hope this makes sense...

    1. Hello!
      The formula below will do the trick for you:

      =IF(ISBLANK(F7),TODAY()-L7,IF(F7>L7,F7-L7,0))

      I hope my advice will help you solve your task.

  5. I am working on a COVID spreadsheet. I have two columns. One for a Symptom On Set Date and one for an exposure date. I need a 3rd column for the Return Date. This date is +11 or +15 based on if it was onset of symptoms or exposure. I want to create an IF Then statement that is something like...
    =IF On set Date is true then add 11 to the cell of on set date or if exposure date is true then add 15 to give me a return date.

    I tried =IF(J2_True,"J2+11",K2_true,"k2+15")

    J2 is my On set date and K2 is my exposure date. It obviously is incorrect. Can you help me? Thank you.

  6. hi, i need help to search in column about value and return raw number.

  7. Hi
    Hope someone can help!
    I am trying to run a formula that will place text in a column based on a upcoming date in another column. For example: If Go live is within 3 business days, (this is column j, with a date of Nov 1) then place "at risk" in column k. Another example. If Go live is within 7 business days, place "upcoming" in column k. The problem is the dates are fluid and I don't want to have to change the datevalue every time the date changes.
    Hope this makes sense.
    Thanks
    Natalie

  8. Thanks for the post.

    I'd like some help on my personal project that involves calculating savings on my savings account. What I like would be to auto calculate on specific dates when my pay comes in and the excel calculates it automatically with any input form me. Thanks.

  9. Is there a formula to find the cell value between two numbers?

  10. Hi, I'm looking for an IF formula that will return HAPPY if the score is greater than or equals 15 and SAD if it is less than 15.

    Thank you.

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      IF(A1>=15,"HAPPY","SAD")

  11. Hi
    I have multiple documents which need to be diffracted using formula

    I want a formula for the Below type.,

    If i have the below documents types., help me with the formula in "__"

    CE It should show "PAYMENTS"
    CM It should show "PAYMENTS"
    RV It should show "INVOICE"
    RR It should show "INCENTIVE"

    Please help me..Appreciate your time and efforts.

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  12. Hello There,

    Im having trouble coming up with the Formula to calculate that If

    A1 has a Invoice Issue Date in and B1 Is to have the Payment received date in but is currently blank.

    how can I have it show me in C1 how many days it has been since the invoice was sent. Up until the point when I have the payment date and then I would like it to stop the count and show me how many days it was between start and finish.

    so to basically have an open counter in days until a final date is put in.

    fingers crossed one of you can help as this has been frying my brain all morning I'm sure its not as complicated as I'm making it out but it has gone beyond me. Many Thanks in advance

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(B1<>"",B1-A1,TODAY()-A1)

      I hope it’ll be helpful.

      1. Thank-you very much first time. And all works perfect. Many Thanks

  13. Hey all. I would like my formula to do the following:

    If a value in 'Table135 Column 2' equals a value in 'Besteltabel Column 1' and there is a value in 'Table135 Column 1' and there as a value in 'Table135 Column 7' and there is no value in 'Table135 Column 8' Then I want it to say yes... if not, I want it to say no.

    The current formula I have doesnt work, but should be along the lines of what Im looking for. Can someone help me out??

    Current formula:
    =IF(Table135[Artikelnummer]=(Besteltabel[[#All];[Artikelnummer]])&(Table135[Datum bestelling]=TRUE)&(Table135[Datum levering]=FALSE)&(Table135[Aantal besteld]=TRUE);"JA";"NEE")

    Thanks in advance.

    1. Oh forgot to mention the following:

      Table 135 - Column 2 = Artikelnummer
      Table 135 - Column 1 = Datum bestelling
      Besteltabel Column 1 = Artikelnummer
      Table 135 - Column 7 = Aantal besteld
      Table 135 - Column 8 = Datum levering

      1. Hello!
        It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
        Unfortunately, without seeing your data it is impossible to give you advice.
        I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
        We'll look into your task and try to help.

        1. Thanks for the response! I've sent you an e-mail.

  14. Hi, please help. what's the formula - I have three columns, if these three has a value, it will appear as completed, if either column 1 and 2 with column 3 has a value, it will appear completed, if only column 3 but there's no value for column 1 and 2, it will appear almost completed.

    another, how can i count the cells with formula but only has value?

    Hope you can help me.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. column1 column 2 column 3
        938,080 1 6001371
        0 1 6001481
        938,080 0 -

        like this one sir, if there's a value in column 1 or 2 then also with column 3, its will appear as completed. But if there's a value in column 1 and 2 and there's none in column 3, it will appear as not completed or vice versa, if column 3 has value and column 1 and 2 has none, it also appear as not completed.

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

          =IF(SUM(IF(A1:C1<>"",COLUMN(A1:C1)))>3,"Completed","Not completed")

          I hope my advice will help you solve your task.

        2. another thing, how can i count the column if there's only 1 on it. it will not count if there's formula but it's zero.

  15. 2. Bob receives a bonus of $50 from the d20 manufacturer each time his sales exceed $500 in a quarter. Write a conditional statement (IF) in cell B11 that will display 50 if the d20 sales are greater than $500 and 0 otherwise. Once you have the formula correct, copy it into C11-E11.

  16. Is there a way to write a formula that says that if a cell has a certain name in it, that I want a series of cells populated (for example - if A2 = SCHOOL then fill B2; AA2)?

    1. Hello!
      In cell B2, you can write the formula

      =IF($A$2="SCHOOL","SCHOOL","")

      Then copy the formula to other cells in your range.

  17. Hello,
    I have the following formula =IF(COUNTA(C8:C12)=5,IF(AND(MIN(C8:C12)>0.999,MAX(C8:C12)<4),"Pass","Fail"),"INCOMPLETE")
    It looks at the data gives "incomplete" if any data is missing, if all Pass, I get a "Pass" result, and if one Fails I get a "Fails" result. However, I would like for the Result to change from "incomplete" to "fails "as soon as one data point Fails. I don't want to wait to take all 5 data points to change from Incomplete to Fails, and if no data points are failing I would like for it to stay as incomplete.
    I hope I explained myself correctly.
    Thanks!

    1. Hello!
      Sorry, I do not fully understand the task. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  18. I have the following formula put in and it works: =IF(G2="PAPR", "X")
    All cells in the column (Column F) contain dates, except where no date is needed, then and X is required, based on if the cell to the right in Column G contains "PAPR" or not. My issue is when I go to drag the formula down in Column F, the dates in the cells disappear and turn to FALSE. How can I apply the formula to all cells in Column F containing dates without the dates disappearing? Thanks!

    1. Hello!
      In a cell, you can write either a value (date) or a formula. You are changing the date to a formula. To conditionally highlight cells, I recommend using conditional formatting.
      If there is anything else I can help you with, please let me know.

  19. Reference No Letter of Credit Bank - Cash 375,000
    Due Date Amount_AED Due Date Amount_AED Over Draft Bank (25,000)
    26,000 15-Oct-20 26,000 15-Oct-20 10,000 Bank Balance 350,000
    28,000 16-Oct-20 28,000 16-Oct-20 14,000
    30,000 17-Oct-20 30,000 17-Oct-20 16,000
    32,000 18-Oct-20 32,000 18-Oct-20 18,000 Salary 90,000
    34,000 19-Oct-20 34,000 19-Oct-20 20,000 Supplier Payments 85,000
    36,000 20-Oct-20 36,000 20-Oct-20 22,000 Dewa 25,000
    38,000 21-Oct-20 38,000 21-Oct-20 24,000 TR 186,000
    40,000 22-Oct-20 40,000 22-Oct-20 26,000 LC 100,000
    42,000 23-Oct-20 42,000 23-Oct-20 28,000 Totl Expences/Liabilites 486,000
    44,000 24-Oct-20 44,000 24-Oct-20 30,000 Reserve (136,000)
    46,000 25-Oct-20 46,000 25-Oct-20 32,000
    48,000 26-Oct-20 48,000 26-Oct-20 34,000
    50,000 27-Oct-20 50,000 27-Oct-20 36,000
    52,000 28-Oct-20 52,000 28-Oct-20 38,000
    54,000 29-Oct-20 54,000 29-Oct-20 40,000
    186,000 100,000 20-Oct-20 286,000

    Need to know the date when my reserve becomes 0 so that we can maintain cash flow based on above example

  20. Hi,
    I'm trying to write a statement that expresses the following:
    C2=A
    D2=G
    E2=Absent
    If cell E2=Absent, then C2/C2 or A/A in this instance but it could be various letters in C2 or D2. If E2=heterozygous, then C2/D2 or A/G in this instance.

    1. Hello!
      Sorry, I do not fully understand the task. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  21. Good day,
    Trying to trim multiple lists of commercial names to common names. Something like xxxxxx™ yyyyyy® zzzzzzzz where xxxxxx would be the common name. Most often ™ comes after the xxxxxx and I can use =TRIM(LEFT(D3,(FIND("™",D3)-1))) but sometimes the ® will be after the xxxxxx. Trying to use an IF formula to find if there is a TM after xxxxxx gives a #VALUE! error. Is there a way to use the #VALUE! as a condition in an IF formula?

    1. Hello!
      I recommend using the SUBSTITUTE function to remove characters

      =TRIM(SUBSTITUTE(SUBSTITUTE(D3,"™",""),"®",""))

      I hope my advice will help you solve your task.

    2. Switched to using IFERROR function and got it to work

  22. i have an issue i want to make an if statement says return 0 tax if the category is ''food'' and return 20% of the amount if the category is not ''food''. Heres how i have written my formular: =IF(C2=''Food'',0,0.2*D2)
    But it has failed to work. its saying invalid on my logical statement (C2=''Food'') but the rest are okay.

    1. Hello!
      Your task is not completely clear to me. The formula is written correctly. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  23. Hi,

    I am looking for a formula which count unique id numbers in multiple rows (for example 5 rows of same id number) however want it to represent 1 for the first record and then 0 for each other (so the unique id is counted as one in the data set).

  24. Im trying to set up a responsibility sheet, cells in Colum A are names chosen from a drop list based from separate contact list. the cells in colum B will be phone numbers. But I want the phone numbers to auto populate based off the name I type in colum A. ie. if A1= Smith B1= 555-0213 . My contact list is a separate sheet in the same workbook.

  25. Hi Experts,
    I am looking for the Customised display with runtime calculated value.
    E.g =IF(I3>0,"Ahead By I3%",IF(I3<0,"Behind By I3%","On Track"))
    Here i want Actual value of I3, instead of String value. Please let me know how to resolve it.

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(I3>0,”Ahead By "& I3 &"%",IF(I3<0,"Behind By "& I3 &"%","On Track"))

      About "&" read this asticle

  26. Hi,

    Thanks for the awesome work!

    I am looking to proiduce a report which only displays a row if there is a value in a range of three (3) cells, so as to eliminate having to scroll through pages of blank data and to minimise my report length.

    Any suggestions?

    Any and all help you offer will be greatly appreciated!

    Kind Regards,
    David

    1. Hello!
      You cannot hide a row using an Excel formula. You need to use VBA macro. If you hide such a line with VBA, you can never write data to it.

  27. Hi,
    Can anyone guide me to correct this formula:

    =IF A2 is >=-107 and A2 is also =-104 and =-100 type High

    Thank You

  28. Hi,
    Can anyone guide me to correct this formula:

    =IF(G2>=-107&G2=-104&G2=-100,"High")))

    Thank You

  29. I would to get a formula that gives 1 if given data in a given cell is more than a specified number. E.g If B1>1600, then show 1

  30. Hi i am trying to write a formula =IF(I2-G2>=3,"HO", IF(I2-G2<4, "CO")) and i want a third option if the cells are blank it will display "-"

    1. Hello!
      Please use the following formula:

      =IF(I2-G2>=3,"HO", IF(I2-G2<4, "CO", IF(AND(I2="",G2=""),"-")))

      Hope this is what you need.

  31. Hello ,applied vlookup to the cells got N/A in multiple cells i use it as values but instead of N/A previous value to fetch and when my cell found new value formula starts from next cells (Which fetch new value as previous value)
    =IF(AND(H:H="#N/A",H3-1),"",IF(H:H=" ",H3))

    1. Resolve this by using IFNA(H3,I2)

  32. Hi, I was trying to ask you a question about adding numbers of certain Rows of a Column if the those Row has a certain keyword. For example column B has the income and Column C has the method(cash or online). and so if i were to sort the income to cash payment and online payment where it would add the column B's Certain Row if that Row's Column C has "Cash" in it.

    1. Hello!
      For me to understand your request better and find a solution, please describe your problem in more detail. Include an example of the source data and the result you want to get. Thank you.

  33. Hi all,

    I've spent hours trying but keep getting error.

    I need a formula for the target grade a child will get depending on a test result. However (and this is the bit I'm stuck on) the result depends on when they sit the test.

    These are my options of times: Y7 baseline, End of Y7, Y8 baseline, End of Y8, Y9 baseline, End of Y9, Y10 baseline, End of Y10, Y11 baseline.

    If they get a 1 in Y7 baseline their result is MTG 3. Whereas if they get a 2 it's MTG 4. However, if they got 2 in year 8 it's MTG U.

    Hope that makes sense.

    Assessment point Result
    Y7 baseline 1 2 3 4 5 6 7
    End Y7/baseline Y8 2 3 4 5 6 7 8
    End Y8/baseline Y9 3 4 5 6 7 8 9
    End Y9/baseline Y10 4 5 6 7 8 9 10
    End Y10/baseline Y11 5 6 7 8 9 10 11

    MTG 3 4 5 6 7 8 9

    I hope there is someone out there who can help me.

    Thanks so much Emma

  34. Hello,
    I'm using the following formula, where I want 30, 20, 10 to appear in the cell as money:
    =IF(C7>=90,"30",IF(C7>=80,"20",IF(C7>=70,"10")))

    After entering the formula I used the $ / currency "button" to change the cell to a dollar amount but it doesn't work. Do I need to add it into the formula? If so, how?

    Thank you!!

    1. And one more question, the above formula =IF(C7>=90,"30",IF(C7>=80,"20",IF(C7>=70,"10"))) shows FALSE in the cell until data is entered in C7. How can i show the cell as blank until data is entered?
      Thank you!!

      1. Hello!
        To use the monetary number format, your formula must return numbers, not text as it is now.
        Change the formula

        =IF(C7>=90,30,IF(C7>=80,20,IF(C7>=70,10,"")))

        I hope my advice will help you solve your task.

        1. It worked perfectly, thank you for your help!!

  35. For the example on Excel IF formula examples for dates:
    - If the date is left blank, what would be the formula?

    I really hope someone can help me. Thanks! :)

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail? Give an example of the source data. What result do you want to get? Thank you!

  36. Hi i am trying to create a formula which will distinguish high, critical orders as well as express air delivery methods, Now i only want TRUE in next column if both the columns have any one of the text in them.

    Order Priority Ship Mode Customers who are urgent
    High Regular Air
    High Express Air
    Critical Express Air
    High Delivery Truck
    Critical Regular Air
    Medium Express Air
    Low Regular Air

    I have been trying the simple if function to show only high or critical. but if order priority is medium or low and ship mode is express air. it shows false. kindly solve my problem.

    1. Hello!
      To check if both columns have text, use the formula

      =IF(AND(A1<>"",B1<>""),TRUE,FALSE)

      or

      =IF(AND(ISBLANK(A1),ISBLANK(B1)),FALSE,TRUE)

  37. HELP!

    I'm trying to create a formula that will recognize a negative figure in an answer and if it is negative then add to it, if the answer is not a negative figure to leave the original answer

    =IF((AND(U24>N4,U24<0)),(U24+N4),(U24-N4))

    1. Hello!
      Sorry, but I don't understand how your formula and question are related. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  38. can you help me!

    What's the formula if a specific number or text will only appear in the cell?

    sample : only number 1, will appear on the cell. IF I PUT OTHER NUMBER IT WILL BECOME ERROR.

    THANK YOU

  39. Hi,

    Please help me to find out a formulae for below mentioned condition.

    if the value is (0-6) = need to calculate the value (number)*5%

    if the value is (7-10) = need to calculate the value as (number)*10%

    But the difficulty is if my value is 8 means first 6 want to calculate as 6*5% and remaining 2 will need to calculate as 2*10%.

    So I want the result as ((6*5%) + (2*10%))

    If any possible way to write a formulae for that. please guide me.

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please use the following formula

      =IF(A1=8,6*0.05+2*0.1,IF(AND(A1>0,A1<=6),A1*0.05,IF(AND(A1>=7,A1<=10),A1*0.1,"")))

      1. Hi Alexander,

        This is not I am expecting,

        0-6 = 5%
        7-12 = 10%
        13-18 = 15%
        19+ = 20%

        For example my count is 20 means first 6 calculate by 5%, 7-12 will be calculate 10%, 13-18 will be calculate 15% ten remaining 2 will calculate 20%.

        Below the detailed condition and Please help me find out this.
        Cnt Value Result Condition
        1 50 $2.50 Value*5%
        2 50 $2.50 Value*5%
        3 50 $2.50 Value*5%
        4 50 $2.50 Value*5%
        5 50 $2.50 Value*5%
        6 50 $2.50 Value*5%
        7 50 $5.00 Value*10%
        8 50 $5.00 Value*10%
        9 50 $5.00 Value*10%
        10 50 $5.00 Value*10%
        11 50 $5.00 Value*10%
        12 50 $5.00 Value*10%
        13 50 $7.50 Value*15%
        14 50 $7.50 Value*15%
        15 50 $7.50 Value*15%
        16 50 $7.50 Value*15%
        17 50 $7.50 Value*15%
        18 50 $7.50 Value*15%
        19 50 $10.00 Value*20%
        20 50 $10.00 Value*20%
        Total $110.00

        1. Hello!
          The formula I gave you is in line with what you wrote earlier. The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
          I recommend paying attention to a similar question.

  40. What I’m trying to express is a valid #3 Statement so that when #2 or #1 statement goes before it, it doesn’t give me an error in the last column that follows them all. The W column.

    So H&R column cells values must be calculated. At all times, which is H10*R10.

    When U&V column cells have values I have to add the all values, which is (H10*R10)+(U10*V10). (If U10 holds no value then V10 will be calculated to 0) which is my issue. I don’t want the 0 in the cell.

    Example 1 formula is column V5. When this formula is input, I see the 0. Example 3 formula is then placed into column W & the value is properly displayed.

    Example 2 formula is an alternative to Example 1 which also goes into column V5. When this formula is input, the 0 is removed in V5 as desired. However this formula clears out all values for column W, & Y which requires it to properly display & it doesn’t.

    Is there a way to nest the formula for Example 3 [H10*R10] always but ONLY when U10 & V10 have values will I use: (H10*R10)+U10*V10, so that I can use Example 2 as it is? I think it’s messing up because V10 can equal 0 & when I use a formula to not have it displayed it throws everything in all WYZ columns off. I hope this was clearer?

  41. Hello,

    I’m having an issue. I’m not sure if this should be a nested IF statements. The first 2 formulas represent the subtraction of dates. The dates can equal 0 to represent no days have passed/a solution was made the same day. I do not want the 0 to populate, I want the cell to remain blank.

    1. =IF(ISBLANK(N10),"",(N10-F10)) This formula subtracts & shows 0 in a following cell. Example 3 formula is calculated properly & all values are showing thereafter in other columns.

    2. =IF(N10-F10=0,"",N10-F10) This formula subtracts & removed the 0 (as desired). Example 3 formula is NOT calculated when the 0 is removed. It then throws off 3 calculations for all other columns & rows to follow.

    3. = IFERROR(IF(ISBLANK(R10),””,(H10*R10)+(U10*V10)),""). This is the formula of the calculated column that is affected perfectly when Example 1 preceded it (non desired 0 in cell) but horribly when Example 2 preceded it (desired blank cell, no 0). I believe the multiplication is throwing it off & the removal of the 0 in Example 2 is causing it. V10 is used to calculate a formula in W10 ONLY when U10 & V10 have values. They will usually be blank.

    Is there a way to put a formula in that will distinguish when to use the “+(U10*V10) like an either or? I only need the second half of that equation when U & V have values. I hope this makes sense...

    1. Hello!
      I don't quite understand your calculations. It would be easier to understand them if you wrote an example of the source data and the expected result. The condition "when U & V have values" can be written as

      U10&V10=""

      It can be used in an IF function.

      1. Sorry I did another question instead of replying but I just figured it out! Thanks for your continued helpfulness, Alexander!

  42. Hello,
    I'd like my IF statement to say:
    If AN23=KS, then type in 6/30/2021, otherwise type in 9/28/2020

    AN23 is linked to another cell and either says KS or MO
    So, the formula should place either 6/30/2021 or 9/28/2020 which are the expiration dates of a state license.

    =IF(AN23="KS",DATEVALUE["6/30/2021"], DATEVALUE [9/28/2020])

    This is what I typed and it doesn't work.

    Thank-you.

    1. Hello!
      Please try the following formula:

      =IF(AN23="KS",DATEVALUE("6/30/2021"), DATEVALUE("9/28/2020"))

  43. I need to work out:

    If a number of years worked is less than 5, then 0
    If a number of years worked is more than 5, then add 1 for every year above 5

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =IF(B1<5,0,(B1-5))

  44. Hi, your explanation and example don't quiet marry up, so I will give you 2 solutions.
    Info:
    9AM is 09:00 ( 9/24)
    5PM is 17:00 (17/24)
    From 5PM to 9AM is +1 day -8 hours or +16 hours (16/24) (2/3)
    #1 After 5PM show Tomorrow 9AM
    IF( ( A1-INT(A1)) > (17/24), INT(A1) +1 +(9/24), A1)
    #2 After 17:xx show Tomorrow 09:xx
    IF( ( A1-INT(A1)) > (17/24), A1 +(2/3), A1)
    # This can get really complex if you want ( 09:00-17:00 M-F, NO Change, Otherwise set time to next work day)

  45. Please i want to know how to use IF function to determine or return only each month names (e.g January , February etc) from a single column of different dates

    1. Hello!
      Your IF formula will be very large. I recommend using the VLOOKUP function to select the name of the month

      =VLOOKUP(MONTH(B1), {1,"January";2,"February";3,"March";4,"April";5, "May";6,"June";7,"July";8,"August";9,"September";10, "October";11,"November";12,"December"}, 2,0)

      I hope this will help

  46. Please assist with this formula, especially with the last criteria. If a student is absent during test week and needs to show up on the Remark Column.
    =IF(W5>84,"An excellent performance. Keep it up!.",IF(W5>64,"A very good performance. Can still improve.",IF(W5>49,"A good performance. There is room for improvement.",IF(W5<50,"Needs a lot of improvement.", IF(W5="ab","Was absent during the test week.")))))

    1. Hello!
      Add a condition to your formula that W5 is a number.

      =IF(AND(ISNUMBER(W5),W5>84),"An excellent performance. Keep it up!.", IF(AND(ISNUMBER(W5),W5>64),"A very good performance. Can still improve.", IF(AND(ISNUMBER(W5),W5>49),"A good performance. There is room for improvement.", IF(AND(ISNUMBER(W5),W5<50),"Needs a lot of improvement.", IF(W5="ab","Was absent during the test week.","")))))

      I hope my advice will help you solve your task.

  47. i have a age list of stuents in column C . age is in yearsmonths and days i.e. 14years,11monts.20 days etc .I wants to calulate under age and over age on a perticular date i.e 01 april 2020.

  48. i have a age list of stuents in column C . age is in yearsmonths and days i.e. 14years,11monts.20 days etc .I wants to calulate under age and over age on a perticular date i.e 01 april 2020. whoes age 1s more then 16 years "overage" and less then 15 year"underage" how can i solve this?

  49. HELLO!
    I am trying to write a formula to fill a cell (say T9) with N/A if the cell J9 includes wording "Standard Type I" or "Standard Type II". The field selections in J9 include but are not limited to
    MBCI Standard Type I 20yr
    MBCI Standard Type II 20yr
    MBCI Single Soucre III 20yr
    and so on

    1. Hello!
      Your condition can be written into a formula

      =IF(OR(ISNUMBER(FIND("Standard Type I",J9,1)),ISNUMBER(FIND("Standard Type II",J9,1))),"N/A","")

      I hope this will help

  50. Hi
    I'm trying to do a check in a spreadsheet and the the IF function is giving me the incorrect result:
    If(D200=AE200,"YES", "NO")
    D200 (this is a formula value of running balance D199 + C200) and AE200 (this is sum of F200:AD200). The value is the same in each cell i.e. 6,603.16 - but gives me a NO result.
    I've tried adding in VALUE before the cell reference but this still does not give me the correct result.

    1. Hello!
      Without seeing your data it hard to give you advice.
      Your numbers may differ in some decimal place. I recommend using the rounding function. Or you can perform calculations with the precision with which the numbers appear in your spreadsheet. To do this, use File-Options-Advanced-When calculating ...- Set pricision as displayed.

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