Skip to main content

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 (

 

Thanks!

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

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

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 pARInvoice.DueDate] to it:

=IIF((IARInvoice.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)

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.

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.

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.

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

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

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

 

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

 

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

 

Any ideas?

'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)

Thanks @darylbowman!  I appreciate the help.

Reply


Terms & ConditionsCookie settings