# Discussions

View Only

## 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.

Top
Contributor
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)