Excel Multiple IF logic scenario.

Topic

New Reply

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 P37/1/16
“Overdue” If P3<7/1/16
"No due date" If P3="" (blank)

IF AN3="Completed" then"
"On time request" IF AM3<=0 (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/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

Reply | Quote

Replies

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:
45045-Status

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

Here's my Test File: 45046-Status

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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”,”Whateverisleft”)))),IF(AN1=”Completed”,IF(P3=””,”No due date”,IF(AM30,”Late request”,”whatelseisleft”)))))
HTH,
Maud

Reply | Quote

Maud,

Nice Job! It reminds me of those stare at this and it’ll start move pictures. 😆 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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 >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(AM30,”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 🙂

Reply | Quote

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 AM30, 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

Reply | Quote

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:
45049-Status

Here's the new test workbook: 45050-Status

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote