Discussions

Expand all | Collapse all

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

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

    Posted 11-02-2017 05:45

    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. 


  • 2.  RE: I am tracking aging of support tickets from two different systems.

    Bronze
    Contributor
    Posted 11-02-2017 22:01
    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)))


  • 3.  RE: I am tracking aging of support tickets from two different systems.

    Posted 11-02-2017 23:24
    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)


  • 4.  RE: I am tracking aging of support tickets from two different systems.

    Posted 11-14-2017 00:33
    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.


  • 5.  RE: I am tracking aging of support tickets from two different systems.

    Bronze
    Contributor
    Posted 11-14-2017 00:47
    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)