I need help to create a formula to calculate a complex duration issue.

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered

I need help to create a formula to calculate a complex duration issue.

I am tracking aging of support tickets from two different systems.

Therefore, I have a total of 4 time stamps.

 I need an overall duration time that contains the following rules.


  • If Target resolution date and Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

  • If Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

    If Actual Resolution Date is not blank, calculate the number of business days between Date Identified and Actual Resolution Date


Photo of Rebekah Alcantar

Rebekah Alcantar

  • 140 Points 100 badge 2x thumb

Posted 9 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
  • If Target resolution date and Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

  • If Actual Resolution Date is blank, calculate the number of business days between Date Identified and today's date

    If Actual Resolution Date is not blank, calculate the number of business days between Date Identified and Actual Resolution Date



Try this:

IF(
isnull([Target resolution date]) and isnull([Actual Resolution Date]), weekdaysub(Today(), [Date Identified]),

isnull([Actual Resolution Date]), weekdaysub(today(), [date Identified]),

not isnull([actual resolution date]), weekdaysub([actual resolution date],[date identified]))