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

  • 0
  • 2
  • Question
  • Updated 1 year 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 3 is blank, then calculate duration between 1 & 2
  • If 3 is NOT blank AND 4 is blank, then calculate duration between 1 & current date.
  • If  3  and  4 are not blank then calculate duration between 1 & 4
Most of the duration formulas are created to work between two dates. I have a total of four date fields with if this - then that scenarios. 
Photo of Rebekah Alcantar

Rebekah Alcantar

  • 140 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 2
if(isnull([date 3]), [date 2]-[date 1],
if(not(isnull([date 3])) and isnull([date 4]),today()-[date 1],
if(not(isnull([date 3])) and not(isnull([date 4])),[date 4]-[date 1],
null)))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
I let Eric do the heavy lifting here.  But you can make the formula more readable like this


IF(
isnull([date 3]), [date 2]-[date 1],

not(isnull([date 3])) and isnull([date 4]),today()-[date 1],

not(isnull([date 3])) and not(isnull([date 4])),[date 4]-[date 1],
null)
Photo of Rebekah Alcantar

Rebekah Alcantar

  • 140 Points 100 badge 2x thumb
Thanks so much!
I need to add one more line..

  • If 3 is blank, then calculate duration between 1 & 2
  • If 3 is NOT blank AND 4 is blank, then calculate duration between 1 & current date.
  • If  3  and  4 are not blank then calculate duration between 1 & 4
  • If  3  and  4 are blank then calculate duration between 1  current date

I tried to create a formula for the new line based on the current coding and kept hitting error messages.
IF(
isnull([date 3]) and isnull([date 4),today()-[date 1],

isnull([date 3]), [date 2]-[date 1],

not(isnull([date 3])) and isnull([date 4]),today()-[date 1],

not(isnull([date 3])) and not(isnull([date 4])),[date 4]-[date 1],
null)