Forum Discussion

RebekahAlcantar's avatar
RebekahAlcantar
Qrew Trainee
8 years ago

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

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. 
Formulas and functions
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    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)))
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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)
  • RebekahAlcantar's avatar
    RebekahAlcantar
    Qrew Trainee
    8 years ago
    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.
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    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)