Help

Help with If Formula & Due Date

Topic Labels: Formulas
1713 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Pedro_de_Nobrig
4 - Data Explorer
4 - Data Explorer

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 2
JT_Kirk
5 - Automation Enthusiast
5 - Automation Enthusiast

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.