Writing a formula that looks at multiple open tickets against systems

Question

I am trying to write a formula that looks at a register of tickets that calculates the length of time the system was down if multiple tickets were open at the same time.

For example Ticket 1 for System 1 was open on 16/01/2025 and closed 20/01/2025 Ticket 2 for System 1 was opened on the 17/01/2025 and closed 20/01/2025

The downtime for the system would be 4 days as that is the difference between the opening date on the oldest ticket and the closing date on the newest however the formula I have is summing the 4 days for ticket 1 and the 3 days for ticket 2 and saying the system was down for 7 days.

   =IF(AU22="","",IF(COUNTIFS('Main Log'!D2:D2000, Dashboard!AU22, 'Main Log'!B2:B2000, "Open")>1,SUMIFS('Main Log'!K2:K2000, 'Main Log'!D2:D2000, Dashboard!AU22,'Main Log'!C2:C2000, MIN('Main Log'!C2:C2000),'Main Log'!C2:C2000, MAX('Main Log'!C2:C2000)),SUMIFS('Main Log'!K2:K2000,'Main Log'!D2:D2000, Dashboard!AU22)
)

)

I have edited in some sample data, in the example below the downtime for System 1 should be the difference between 23/01/2025 and 17/02/2025 as they are the unique days. The formula however is telling me System 1 was down for 40 days.

Ticket Number	Ticket Status	System	Ticket Start Date	Ticket Resolution Date	System Install Date	Age of System at Time of Ticket Raised (Days)	Ticket Resolution Time (Days)
10208	Closed	System 1	23/01/2025	12/02/2025	09/04/2024	212	20
10368	Open	System 2	14/02/2025	19/03/2025	28/09/2021	1235	33
10242	Open	System 1	28/01/2025	17/02/2025	09/04/2024	1466	20

I have managed to tie myself in a knot with this so any help would be appreciated.

So, say ticket 2 was closed on 21/01/25, would that mean 5 days? — Excellor
– Excellor, Commented Feb 18 at 12:13
Yes exactly, the formula above however would say 8 as it would sum the total time the two tickets were open instead of only calculating the unique days — Strexxin
– Strexxin, Commented Feb 18 at 12:15

Excellor · Accepted Answer · 2025-02-18 13:06:41Z

0

With Excel 365:

=LET(_min,MINIFS($E$2:E5,$E$2:E5,"<"&F5,$F$2:F5,">"&MIN($F$2:F5),$D$2:D5,$K$1),
_max,MAXIFS($F$2:F5,$F$2:F5,MAXIFS($F$2:F5,$D$2:D5,$K$1)),
_max-_min)

Without:

=(MAXIFS($F$2:F5,$F$2:F5,MAXIFS($F$2:F5,$D$2:D5,$K$1))-MINIFS($E$2:E5,$E$2:E5,"<"&F5,$F$2:F5,">"&MIN($F$2:F5),$D$2:D5,$K$1))

Well they're somewhat the same, but IMO LET() makes it eassier to edit your formula.

.

So what it does:

The _min argument looks for the lowest value, that is lower than your last value, and is part of system 1;

the _max argument looks for the highest value, that's part of system 1;

then it's simple substraction.

K1 is input of system, K2 and K3 are output according to the above formula.

answered Feb 18 at 13:06

Excellor

9911 gold badge3 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing a formula that looks at multiple open tickets against systems

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related