1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel IF AND formula and more

Discussion in 'Business Applications' started by holymoly, Jan 31, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    I currently have a formula that takes the current day, and compares to an Order Picked date.

    The formula calculates is the difference between the dates is more than 2.5, "Overdue" is returned in the cell

    The problem I encounter is on Mondays and Tuesdays, where I need to change the formula manually to 4.5 and 3.5
    because the Sat and Sunday are not skipped and no orders are sent as those are off days. So an order picked on Friday
    would show as Overdue on Monday as it is more than 2.5 days.

    Original Formula : =IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))

    The new Formula: =IF(AND($D$3=”Monday”,A2+4.5<$C$3),”Overdue”, IF(AND($D$3=”Tuesday”,A2+3.5<$C$3),”Overdue”, IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))))

    has not worked. Perhaps wrongly positioned parenthesis? It returns #NAME#

    A sample spreadsheet is attached.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    you do an IF for weekday
    =WEEKDAY(A2,2) and if that returns a 6 or 7 then its a weekend

    so for monday =1 ,tuesday =2

    The new Formula: =IF(AND(WEEKDAY($D$3,2)=1,A2+4.5<$C$3),&#8221;Overdue&#8221;, IF(AND(WEEKDAY($D$3,2)=2,A2+3.5<$C$3),&#8221;Overdue&#8221;, IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))))

    can you post a sample spreadsheet with dummy data - as not sure if you have the date in $D$3 or the day of the week
     
  3. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    OK, I did attach previously, but will again with the update. Returning #value#.

    I'm sure monday=1 needs to be defined first


    Never heard of the WEEKDAY function till now.
     

    Attached Files:

  4. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    =IF(AND(WEEKDAY($D$3,2)=1,A2+4.5<$C$3),”Overdue”, IF(AND(WEEKDAY($D$3,2)=2,A2+3.5<$C$3),”Overdue”, IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))))

    OK, change $D$3 to

    SC$3 ?
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    sorry , I missed the spreadsheet attachment dah!!

    so I have modified the formula to use today() and so you dont need cell C, D, G or H - its all calculated in the IF statement - see attached

    =IF(AND(WEEKDAY(TODAY(),2)=1,A2+4.5<TODAY()),”Overdue”, IF(AND(WEEKDAY(TODAY(),2)=2,A2+3.5<TODAY()),”Overdue”, IF((A2+2.5)<TODAY(),"Overdue",IF((A2)>TODAY(),"Future",""))))
     

    Attached Files:

  6. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Thanks Wayne, again, never would have done it without you.

    (y)
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    your welcome (y) thanks for letting us know
     
  8. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Seems the formula not acting correctly, though it appears so.

    Look at B3, C3. According to dictates of formula, they should not return Overdue.

    maybe the weekday function for Monday is not being recognized.

    See attached.

    If you look down Col B, older items that should be overdue, return #NAME.

    We need some fine tuning?
     

    Attached Files:

  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    the inverted commas on the 1st two overdue statements are not correct
    slanted rather than
    "

    i just edited the spreadsheet = see attached
     

    Attached Files:

  10. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    A slant can make a difference? :eek:

    OK, that said, the second and third fomulae, B3 and B4, the result should be blank cell, not Overdue.

    B3 rationale Feb 8 + 4.5 = 12.5 . this is not less than Today which is Feb 11

    B4 Feb 7 + 4.5 = 11.5, this is not less than Today which is Feb 11

    Please see C3, C4. They correctly return FALSE.

    Something wrong with formula, that it returns Overdue.
     

    Attached Files:

  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    yep, different character as far as excel is concerned

    i'll look
     
  12. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    I attached the file.

    Look at C3, C4
     
  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    looks like we have something wrong in the tests for TRUE

    here are the tests
    Code:
    AND(WEEKDAY(TODAY(),2)=1,A3+4.5<TODAY())
    AND(WEEKDAY(TODAY(),2)=2,A3+3.5<TODAY())
    (A3+2.5)<TODAY()
    (A3)>TODAY()
    
    and the results are
    FALSE
    FALSE
    TRUE
    FALSE

    so have we got this wrong
    (A3+2.5)<TODAY()

    should it be
    (A3+2.5)>TODAY()

    does that make sense?

    Code:
    =IF(AND(WEEKDAY(TODAY(),2)=1,A32+4.5<TODAY()),"Overdue", IF(AND(WEEKDAY(TODAY(),2)=2,A32+3.5<TODAY()),"Overdue", IF((A32+2.5)<TODAY(),"Overdue",IF((A32)>TODAY(),"Future",""))))
    see row 32 on attached spreadsheet
     

    Attached Files:

  14. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Thanks for your response.

    The test results in cells B24 and B25 are correct, they return FALSE.

    Therefore Cells B42, B43 should return blank, but they return Overdue.
    This is puzzling since they failed the test for Overdue.
     
  15. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    i have broken the whole thing out
    so you can see each test and the result true false and also the date the formula is creating

    i have added some conditional format to the B column
    overdue = red
    future = green
    blank - fill grey

    also columns D, E, F, G
    has conditional format fill for true and false

    D,E,F,G
    has each of the tests - true and false - so we can see whats happening for each test

    I, J shows the 2 parts of test1
    L, M shows the 2 parts of test2
    O shows test3
    Q shows test4

    so in the logic you require - where is it actually going wrong - what part of the test

    should be able to see from the breakdown

    to get a blank all 4 tests need to return a FALSE
    future will only be applied if TEST4 is TRUE

    its going to change each day , and due to our time zones , we may not see the same cells

    but you posted on tuesday

    at the moment tuesday 12th
    it show the blank result against 10, 11 & 12th Feb

    so you want the dates 8 and 9th to also be blank
    which means test 2 and test 3 needs to change as they are showing TRUE and so will return "overdue"

    we can change each section of the tests to get the correct result you need now

    Remember nested IFs work through the sequence in order , and i have set up for a true result to show overdue/future

    IF TEST1 = TRUE then overdue formula stops - IF FALSE move to TEST2
    IF TEST2 = TRUE then overdue formula stops - IF FALSE move to TEST3
    IF TEST3 = TRUE then overdue formula stops - IF FALSE move to TEST4
    IF TEST4 = TRUE then future formula stops - IF FALSE then show a blank cell - a blank cell only occurs when all 4 tests are FALSE
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1087690

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice