CODEWITHSUNDEEP
excelformula
Strexxin
Strexxin

Reputation: 29

Writing a formula that looks at multiple open tickets against systems

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.

Upvotes: 0

Views: 56

Answers (1)

Excellor
Excellor

Reputation: 875

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.

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

Upvotes: 0

Related Questions