Skip to main content

Hi There


Looking for help with a formula.


I have a column (Inv Date) that is populated with Invoice Dates.

I have a 2nd column (Payment Received) which has a multiple select list consisting of Paid, Courtesy, Consignment, Credit or it is blank meaning no payment has been received yet.


In a new column (Past Due) I would like to create a formula whereby if the (Payment Received) column is populated with Paid it will then populate the (Past Due) column with Paid. If the (Payment Received) column is blank then it will populate the (Past Due) column with however many days it is past the invoice date.


Please can you help.


Thanks so much.

Here is a version using Nested If statement. The 2 in DATEADD is for a grace period.


IF(T({Payment Received}) = "Paid", 

  "Paid", 

  IF(T({Payment Received}) = BLANK(), 

    IF(OR(T({Invoice Date}) = BLANK(), DATEADD({Invoice Date}, 2, 'days')>NOW()), 

      "blank", 

      DATETIME_DIFF(NOW(), DATEADD({Invoice Date}, 2, 'days'), 'days'))

  )

)

Here is a version using Nested If statement. The 2 in DATEADD is for a grace period.


IF(T({Payment Received}) = "Paid", 

  "Paid", 

  IF(T({Payment Received}) = BLANK(), 

    IF(OR(T({Invoice Date}) = BLANK(), DATEADD({Invoice Date}, 2, 'days')>NOW()), 

      "blank", 

      DATETIME_DIFF(NOW(), DATEADD({Invoice Date}, 2, 'days'), 'days'))

  )

)

Thanks so much for all your help, it worked exactly as I wanted it to.


Really appreciate it.


Thanks again.

Reply


Terms & Conditions