Skip to main content

Help with If Formula & Due Date

  • March 13, 2019
  • 2 replies
  • 20 views
P

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.

2 replies

J
  • JT_Kirk
  • New Participant
  • March 13, 2019

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'))
  )
)
P
  • Pedro_de_Nobrig
  • Author
  • New Participant
  • March 14, 2019

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.

Terms & ConditionsAccessibility statement