Formula help in a GI

  • 25 September 2023
  • 11 replies

Userlevel 2

I am getting a syntax error with this formula and can’t quite figure it out.

=IIF ((DateDiff(interval, [ARInvoice.DueDate], Today()) >=30 and (DateDiff(interval, [ARInvoice.DueDate], Today())<60, [ARInvoice.CuryDocBal],0)

I am trying to create columns of data depending on the “aging” of the invoice balance in a GI so that we can export to Excel for submission to our Auditors.  The value should either be zero or the invoice balance.

I have this similar formula that works fine for current invoices:  

=IIF ([ARInvoice.DueDate] >= Today(),[ARInvoice.CuryDocBal],0)




Best answer by palbores 25 September 2023, 18:51

View original

11 replies

Userlevel 2

Thanks @darylbowman!  I appreciate the help.

Badge +12

'IS NULL' is not a valid statement.

What about this:

=IIF(DateDiff('d', [ARInvoice.DueDate], Now()) < 30 OR [ARInvoice.DueDate]=Null, IIF([ARInvoice.DocType] = 'CRM', [ARInvoice.CuryDocBal] * -1, [ARInvoice.CuryDocBal]), NULL)

Userlevel 2

@darylbowman I must be missing something simple.  Here is my current formula: 


=IIF(DateDiff('d', [ARInvoice.DueDate], Now()) < 30 OR [ARInvoice.DueDate] IS NULL, IIF([ARInvoice.DocType] = 'CRM', [ARInvoice.CuryDocBal] * -1, [ARInvoice.CuryDocBal]), NULL)


I get a message: “The method or operation is not implemented.”


Any ideas?

Badge +12

You could do an IIf condition to determine if it ARInvoice.DocType is a Credit Memo and if so, multiply the total by -1.

Userlevel 2

The credit memo hasn’t been applied to anything.  The credit memo may also be the only thing on the customer's account.

Badge +12

If the adjustments have been applied (ie: credit memos or payments), the remaining balance should be reflected in ARInvoice.CuryDocBal. You shouldn't need to manually calculate it.

Userlevel 2

Any idea how to get a GI to Sum a credit memo properly along with Invoices?  Currently what I have going adds the credit memo to the total vs what you would expect as lowering the total due.

Badge +12

Small note that DateDiff will compare dates in either direction (X days greater OR less)

I fact-checked myself and it turns out that the result of DateDiff is actually signed (+/-), which means the order that you enter the dates will determine if the result is positive or negative. The result could be confusing, but regardless, it would be clear that 30 days in the future is not the same as 30 days in the past.

Badge +12

Small note that DateDiff will compare dates in either direction (X days greater OR less). A more exact approach would be to use DateAdd(Today(),’d’,-30) (30 days less than today) and compare [ARInvoice.DueDate] to it:

=IIF(([ARInvoice.DueDate] < DateAdd(Today(),'d',-30)) And ([ARInvoice.DueDate] >= DateAdd(Today(),'d',-60)), [ARInvoice.CuryDocBal], 0)

(the Invoice has been due for 31-60 days, which is what I am assuming you’re looking for)

Userlevel 2

Thanks @palbores!  That works for what I was trying to do.

Userlevel 3

Hi @swittrock, Try this formula below.

=IIF ((DateDiff('d', [ARInvoice.DueDate], Today()) >=30) And (DateDiff('d', [ARInvoice.DueDate], Today())<60), [ARInvoice.CuryDocBal],0)
  • interval = d - for day
  • I added a close parenthesis on your formula which is missing


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved