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

• 0
• Question
• Updated 2 years ago

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.
• 140 Points

Posted 2 years ago

• 0
• 4,022 Points
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)))
• 75,144 Points
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)
• 140 Points
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.
• 4,022 Points
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)