Excel IF statement

Epicor ERP 9
1

epicor version 9.05.701

I appreciate this isn’t directly epicor but its epicor related!

M = receipt date
P = promise date

Current formula =IF(M2="","",IF(M2<=P2,“Yes”,“No”))

So the above says if the receipt date is blank give me blank otherwise if the receipt date is less than the promise date give me “Yes” (meaning on time)…otherwise “No”.

I need to add to this formula to say "if the receipt date is blank AND the promise date has passed (gone past today) then also return “no”.

Many thanks in advance.

2

The Syntax for IF in Excel is pretty straight forward
=IF (logical_test, [value_if_true], [value_if_false])
you can combine these… in parenthesis…

=IF (logical_test, (=IF (logical_test, [value_if_true], [value_if_false])), (=IF (logical_test, [value_if_true], [value_if_false])))

So you should be able to take the current formula and modify it as such
A2 = M
B2 = P

=IF(AND(A2="",B2<TODAY()),"NO",IF(A2="","",IF(A2<B2,"YES","NO")))
2 Likes
3

Looks good. But I think it would be B2>TODAY() to show the promise date is past due?

Mark,
Wouldn’t you want Receipt date less than or equal to the promise date to show “yes”?

Thanks,
-Tyson

2 Likes
4

Hi,
So just to confirm…yes the promise date greater than today().
And the receipt date can be on time if it’s less than Or equal to…so to show yes.

5

Flip around some >< and it should work :slight_smile:

1 Like
6 
=IF(AND(M2="",P2<TODAY()),"NO",IF(M2="","",IF(M2<=P2,"YES","NO")))

The above seems to have done the trick so thank you both for your comments…

  1. If the receipt is less than or equal to the promise date it shows “yes”.
  2. If the receipt is blank but the promise date hasn’t yet been reached it shows blank.
  3. If the receipt is greater than the promise date it shows “no”.
  4. If the receipt is blank but the promise date has passed it shows “no”.
  5. If the receipt and promise dates are blank it shows “no”.*
7

Looks like you got your EXCEL answer, but thought I would throw out some other answers in C# and SQL… Those that are on E10 can use these types of simple one-liner if statements as well (but often dont realize it)

In C#, to do a single line IF statement, like Excel, you do the following:
(This is saying "If A equals B, then return C, else return D)

  MyAnswer = (A==B) ? C : D;

(This is saying "If A equals B, then return C, else if A > B return E, else return F)

  MyAnswer = (A==B) ? C : (A>B) ? E : F ;

(This is saying "If A equals B AND C equals D return X else return Y)

  MyAnswer = (A==B && C==D) ? X : Y ;

But in BAQs, the conditions are done in SQL format… so in a calculated field you could say:

   iif(A=B,C,D)

   iif(A=B,C,iif(A>B,E,F))

   iif (A=B and C=D,X,Y)
1 Like