# Thread: Excel Multiple IF logic scenario.

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

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

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

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

musical1 (2016-07-22)

5. Maud,

Nice Job! It reminds me of those stare at this and it'll start move pictures.

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

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

#### Posting Permissions

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