Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel Multiple IF logic scenario.

    I have the following logic for which I have been valiantly attempting an IF statement for. As I have been unsuccessful - I am here.


    Cell AN3 has a value of "On-going" or "Completed"
    IF AN3=“On-going” then:
    "On time" If P3>7/31/16
    "In the month" If P3<7/31/16 AND >7/1/16
    "Overdue" If P3<7/1/16
    "No due date" If P3="" (blank)

    IF AN3="Completed" then"
    "On time request" IF AM3<=0 (< OR =)
    "Late request" IF AM3>0
    "No due date" If P3="" (blank)





    I'm not sure my failed attempts will be of any use, but my most recent attempt was:
    =IF(AN3="On-going",IF(P3="","No Due Date",IF(P3<"7/1/16","Overdue",IF(P3>"7/31/16)","On Time","Due in the Month"))),IF(P3="","No Due Date",IF(AM3>0,"Late Request","On Time Request")))

    There seems to be 1 scenario to slip through the cracks no matter which way I turn.

    Thanks in advance for the assistance.
    -Jody
    Last edited by musical1; 2016-07-22 at 15:30.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jody,

    Although you could probably fiddle around with the nested ifs it will drive you nuts, well it does me anyway!

    IMHO you'd be much better off with a user defined function (UDF) where the logic is much clearer plus you get the advantage of being able to reuse from many different cells with a simple call.

    Code:
    Function Status() As String
    
      Application.Volatile
      
      If ([p3] = "") Then
        Status = ""
      Else
      
        If (UCase([AN3]) = "ON-GOING") Then
           Select Case [p3].Value
               Case Is > DateValue("07/31/2016")
                   Status = "On time"
               Case Is > DateValue("6/30/2016")
                   Status = "In the month"
               Case Is < DateValue("07/01/2016")
                   Status = "Overdue"
               Case Else
                   Status = ""
           End Select
        Else
           If ([AM3] <= 0) Then
             Status = "On time request"
           Else
             Status = "Late Request"
           End If
        End If
        
      End If
      
    End Function
    You simply enter the formula: [noparse] =Status()[noparse] in the cell where you want the status to display, you didn't specify this by the way.

    Example:
    Status.PNG

    Note: I hid a lot of columns to make the example use the same addresses as you posted.

    Here's my Test File: Status.xlsm

    Note: The code can be easily changed to Calculate the test dates based on a date cell in the workbook so you could easily change the target dates w/o having to adjust the code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Try this:

    =IF(AN1="On-going",IF(P3="","No due date",IF(P3>42582,"On time",IF(AND(P3>42552,P3<42582),"In the month",IF(AND(P3<42552,P3<>""),"overdue","Whatever isleft")))),IF(AN1="Completed",IF(P3="","No due date",IF(AM3<=0,"On time request",IF(AM3>0,"Late request","whatelseisleft")))))
    HTH,
    Maud
    Last edited by Maudibe; 2016-07-22 at 17:40.

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    musical1 (2016-07-22)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Nice Job! It reminds me of those stare at this and it'll start move pictures.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank Maud - That was exactly what I was looking for.
    I came close a couple of times. My downfall was the P3<>"" segment. It created an air leak.

    A couple of things happened that I had to fix. First-my logic was a tiny off. The >< date range needed = in there too. I fixed that.

    But, what was interesting that happened. I had 3 [on-going] scenarios that weren't coming up with the correct result. as it turns out, 3 of my dates were not formatted as dates. That gave incorrect results. All fixed (and date formatted), the final formula was:

    =IF(AN3="On-going",IF(P3="","No due date",IF(P3>42582,"On time",IF(AND(P3>=42552,P3<=42582),"In the month",IF(AND(P3<42552,P3<>""),"overdue","Whatever isleft")))),IF(AN3="Completed",IF(P3="","No due date",IF(AM3<=0,"On time request",IF(AM3>0,"Late request","whatelseisleft")))))

    RG - This was an interesting approach. I agree the nested IFs were driving me crazy. At least I actually came really close on a couple of my attempts. It very well may have been my date formatting that tripped me.

    I considered your approach, but I had zero clue how to get there.
    Although I didn't specify in my original post, your solution only solves 1 row?

    When I tried it, I got the same results for every row. I believe everywhere I paste the '=status()', it is giving the results for P3?

    Thank you to both! I will sleep better tonight

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    IMHO, I like RG's approach with a UDF. It is so much easier to debug than nested if statements. However, the formula does seem to work. The if P3="" needs to be the first statement in its nested group of it will never have a chance to be evaluated because one of the prior statements will evaluate to TRUE:

    IF AM3<=0 ,IF AM3>0, P3=""

    My question is, in the first segment, what is the formula supposed to return if P3 is not blank and AN1 has something other than a date ("WhatElseIsLeft")?

    Maud

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Musical,

    As I said if you had rows it could be adjusted. Here's a version that does everything automatically including calculating the dates based on the current month.

    Code:
    Option Explicit
    
    Function Status(lRow As Long) As String
     
      Dim iCurMonth As Integer
      Dim dteSOM    As Date
      Dim dteEOM    As Date
       
      Application.Volatile
      
      iCurMonth = Month(Date)
      
      If (iCurMonth = 12) Then
        dteSOM = DateValue(iCurMonth & "/1/" & Year(Date)) - 1
        dteEOM = DateValue("1/1/" & Year(Date) + 1)
      Else
        dteSOM = DateValue(iCurMonth & "/1/" & Year(Date)) - 1
        dteEOM = DateValue(iCurMonth + 1 & "/1/" & Year(Date))
      End If '(IcurMonth
      
      If (Cells(lRow, 16).Value = "") Then
        Status = ""
      Else
      
        If (UCase(Cells(lRow, 40)) = "ON-GOING") Then
        
           Select Case Cells(lRow, 16).Value
               Case Is >= dteEOM
                   Status = "On time"
               Case Is > dteSOM
                   Status = "In the month"
               Case Is <= dteSOM
                   Status = "Overdue"
               Case Else
                   Status = ""
           End Select
           
        Else
        
           If (Cells(lRow, 39) <= 0) Then
             Status = "On time request"
           Else
             Status = "Late Request"
           End If
           
        End If
        
      End If
      
    End Function  'Status
    Sample:
    Status.PNG

    Here's the new test workbook: Status.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Just for info:
    In Excel 2016 the new =IFS(..) function allows up to 127 different conditions to be tested.
    The function returns a value that corresponds to the first TRUE condition.

    IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

    If no TRUE conditions are found, this function returns #N/A error, so you can always include a default value as your last condition, e.g
    =IFS(condition1, result1, condition2, result2, .. , condition x, result x, ..,1=1,"missing data")

    ..just thought you might like to know.

    zeddy

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Further info:

    BEWARE!
    ..those new Excel 2016 functions are only available if you have the subscription version of Excel 2016 e.g. as part of Office365 subscription.

    ..if you send an Excel2016 file which uses these new functions to someone who has paid for Microsoft Office Professional Plus 2016, their copy of Microsoft Excel 2016 will show #NAME? errors as those functions are NOT available!

    zeddy

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    ..those new Excel 2016 functions are only available if you have the subscription version of Excel 2016
    Here is the format for the new 2016 IFS function:
    =IFS(concept="Microsoft", "Makes no sense", createdby="Microsoft", "Makes no sense", implementedby="Microsoft", "Makes no sense")

    incompatibilities between versions is an obstacle. Incompatibilities within the same version makes no sense
    Last edited by Maudibe; 2016-08-07 at 02:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •