Calculate difference in dates from 3 different columns depending on status in 4th column

Platform discussions
, , ,
1

I am creating a board with a formula column to calculate the time between intake and payment. The problem is, payment information is entered in one of two columns depending on payment method. The status choices are: Check, ACH, or Wire. Additionally, some requests are denied, so no payment is made.

If the request is paid by check or ACH, then accounting enters the date in a column labeled Date-Check/ACH Payment, if it is paid by wire transfer, then accounting enters the date in a different column labeled Date-Wire Transaction, and if no payment is made, no date is entered in either column.

The imbedded IF formula I tried is:
IF({Payment Method}=“Check”,(ROUND(WORKDAYS({Date - ACH/Check Disbursement},{Intake Date}),0)-1),IF({Payment Method}=“ACH”,(ROUND(WORKDAYS({Date - ACH/Check Disbursement},{Intake Date}),0)-1),IF({Payment Method}=“Wire”,(ROUND(WORKDAYS({Date- Wire Transaction},{Intake Date}),0)-1),“N/A”)))

Unfortunately, this does not work. Can anyone offer help?

2

Let me know if this works. If it doesn’t, also double check the names of the date columns (the spacing is inconsistent, so make sure that everything matches what’s on the board).

SWITCH(
  {Payment Method},
  "Check",
  WORKDAYS(
    {Date - ACH/Check Disbursement},
    {Intake Date}
  ),
  "ACH",
  WORKDAYS(
    {Date - ACH/Check Disbursement},
    {Intake Date}
  ),
  "Wire",
  WORKDAYS(
    {Date- Wire Transaction},
    {Intake Date}
  ),
  "N/A"
)
1 Like
3

Thank you so much, @FrancisElliott ! That worked like a charm!

1 Like