Calculating the unique days across overlapping date ranges

S

Strexxin

New Member
Joined
Feb 18, 2025
Messages
6
Office Version
  1. 365
Good day,

I am having some problems writing out a formula to calculate the number of unique days a system has been down if there are 2 or more tickets raised against it at the same time. In the below example data the result for System 1 being down would be 22 days as that is the difference between the start date on the first ticket and the end date on the second ticket. However every formula I write calculates each ticket individually and gives me a downtime of 32 days.



Ticket Number		 Serial NumberSystem TypeTicket Start DateResolution Date
1234SN0001System 109/01/202528/01/2025
5678SN0001System 118/01/202531/01/2025

My current attempt is
=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)


I have managed to tie myself in knots so any help on this would be appreciated
 
Sort by date Sort by votes
Welcome to the MrExcel forum!

Try:

Book1
ABCDEFGH
1Ticket NumberSerial NumberSystem TypeTicket Start DateResolution DateSystem TypeUnique Days
21234SN0001System 11/9/20251/28/2025System 123
35678SN0001System 11/18/20251/31/2025System 224
41111SN0002System 22/1/20252/10/2025System 31
52222SN0003System 32/2/20252/2/2025
63333SN0002System 22/20/20252/23/2025
74444SN0003System 32/2/20252/2/2025
85555SN0002System 22/8/20252/24/2025
9
10
Sheet1
Cell Formulas
RangeFormula
H2:H4H2=LET(syscol,$C$2:$C$20,startcol,$D$2:$D$20,ResCol,$E$2:$E$20,sys,G2,mn,MINIFS(startcol,syscol,sys),mx,MAXIFS(ResCol,syscol,sys),s,SEQUENCE(mx-mn+1,,mn),SUM(SIGN(COUNTIFS(syscol,sys,startcol,"<="&s,ResCol,">="&s))))
 
Upvote 0
Solution
Eric W said:
Welcome to the MrExcel forum!

Try:

Book1
ABCDEFGH
1Ticket NumberSerial NumberSystem TypeTicket Start DateResolution DateSystem TypeUnique Days
21234SN0001System 11/9/20251/28/2025System 123
35678SN0001System 11/18/20251/31/2025System 224
41111SN0002System 22/1/20252/10/2025System 31
52222SN0003System 32/2/20252/2/2025
63333SN0002System 22/20/20252/23/2025
74444SN0003System 32/2/20252/2/2025
85555SN0002System 22/8/20252/24/2025
9
10
Sheet1
Cell Formulas
RangeFormula
H2:H4H2=LET(syscol,$C$2:$C$20,startcol,$D$2:$D$20,ResCol,$E$2:$E$20,sys,G2,mn,MINIFS(startcol,syscol,sys),mx,MAXIFS(ResCol,syscol,sys),s,SEQUENCE(mx-mn+1,,mn),SUM(SIGN(COUNTIFS(syscol,sys,startcol,"<="&s,ResCol,">="&s))))
Click to expand...
Thank you for the response, the result of this formula on the actual data comes back as 0 whereas it should be 25 when I adjust the formula ranges. Why would this be do you think?
 
Upvote 0
Hard to say without seeing your data. Are the dates entered as actual dates, or as text? Is the system type spelled EXACTLY the same everywhere? No hidden spaces or anything? If you could show a sample of your data, that would help. Check out the xl2bb Tool, which is what I used to display my sheet. Click on the link in my signature, or in the response box. It's easy to download, install, and use.
 
Upvote 0
System Issues Log V3.xlsx
ABCDEFGHIJK
1Ticket NumberTicket StatusTicket OwnerSerial NumberSystem TypeSystem LocationTicket Start DateTicket Resolution DateSystem Install DateAge of System at Time of Ticket Raised (Days)Ticket Resolution Time (Days)
210208ClosedGM0683RADiant Aura 50Kv23/01/202512/02/202525/06/202421220
310368OpenGS105CIX314/02/2025 28/09/20211235 
410074Opengs099CIX319/12/2024 14/12/20201466 
510113ClosedGM0379X15002/01/202517/02/202525/07/2014381446
610235OpenGM0517RADiant 80Kv27/01/2025 16/05/20192083 
710242ClosedGM0683RADiant Aura 50Kv28/01/202517/02/202525/06/202421720
810190Open2020-0879SARRP20/01/2025    
910331OpenGM0640X20010/02/2025 20/06/2024235 
109954OpenGM0536X30002/12/2024 27/08/20211193 
1110287OpenGM0564X20004/02/2025 19/03/20211418 
1210049OpenGM0450Medical Software16/12/2024 15/09/20172649 
1310198Opengm0522 21/01/2025 15/11/20191894 
1410199Open2016-0755SARRP21/01/2025 29/08/20163067 
1510168ClosedGM0528RADiant 50Kv15/01/202518/02/202522/06/2021130334
1610026ClosedGM0528RADiant 50Kv10/12/202418/02/202522/06/2021126770
179358ClosedGM0469X15011/09/202418/02/202520/07/20182245160
1810384Open2010-0505SARRP18/02/2025    
199909ClosedGM0663X20021/11/202418/02/202528/06/202414689
2010069OpenGS147CIX318/12/2024 00/01/190045644 
2110193OpenGM0469X15020/01/2025 20/07/20182376 
2210318OpenGM0086X15006/02/2025 06/11/20047397 
Main Log
I have posted a sample of the data below, I have removed data from some of the columns as it contains sensitive information.
Cell Formulas
RangeFormula
A2:A22A2=IF('Automated Ticket Info'!A2="","",'Automated Ticket Info'!A2)
B2:B22B2=IF([@[Ticket Number]]="","",IF([@[Ticket Resolution Date]]="","Open","Closed"))
D2:E22,G2:H22D2=IF('Automated Ticket Info'!B2="","",'Automated Ticket Info'!B2)
I2:I22I2=IF(ISNUMBER(SEARCH("Aura",E2)),XLOOKUP(D2,'US System Data'!$A$2:$A$75,'US System Data'!$E$2:$E$75,""),IF(XLOOKUP(D2,'UK System Data'!$A$2:$A$1500,'UK System Data'!$E$2:$E$1500,"")="",XLOOKUP(D2,'UK System Data'!$A$2:$A$1500,'UK System Data'!$D$2:$D$1500,""),XLOOKUP(D2,'UK System Data'!$A$2:$A$1500, 'UK System Data'!$E$2:$E$1500, "") ) )
J2:J22J2=IFERROR([@[Ticket Start Date]]-[@[System Install Date]],"")
K2:K22K2=IFERROR([@[Ticket Resolution Date]]-[@[Ticket Start Date]],"")
 
Upvote 0
It seems to be working for me:

Book1
ABCDEFGHIJKLMNO
1Ticket NumberTicket StatusTicket OwnerSerial NumberSystem TypeSystem LocationTicket Start DateTicket Resolution DateSystem Install DateAge of System at Time of Ticket Raised (Days)Ticket Resolution Time (Days)System TypeUnique Days
210208ClosedGM0683RADiant Aura 50Kv1/23/20252/12/202525/06/202421220CIX30
310368OpenGS105CIX32/14/202528/09/20211235Medical Software0
410074Opengs099CIX312/19/202414/12/20201466RADiant 50Kv71
510113ClosedGM0379X1501/2/20252/17/202525/07/2014381446RADiant 80Kv0
610235OpenGM0517RADiant 80Kv1/27/202516/05/20192083RADiant Aura 50Kv26
710242ClosedGM0683RADiant Aura 50Kv1/28/20252/17/202525/06/202421720SARRP0
810190Open2020-0879SARRP1/20/2025X150161
910331OpenGM0640X2002/10/202520/06/2024235X20090
109954OpenGM0536X3002/12/202427/08/20211193X3000
1110287OpenGM0564X2002/4/202519/03/2021141800
1210049OpenGM0450Medical Software12/16/202415/09/20172649
1310198Opengm05221/21/202515/11/20191894
1410199Open2016-0755SARRP1/21/202529/08/20163067
1510168ClosedGM0528RADiant 50Kv1/15/20252/18/202522/06/2021130334
1610026ClosedGM0528RADiant 50Kv12/10/20242/18/202522/06/2021126770
179358ClosedGM0469X1509/11/20242/18/202520/07/20182245160
1810384Open2010-0505SARRP2/18/2025
199909ClosedGM0663X20011/21/20242/18/202528/06/202414689
2010069OpenGS147CIX312/18/202400/01/190045644
2110193OpenGM0469X1501/20/202520/07/20182376
2210318OpenGM0086X1502/6/20256/11/20047397
23
Sheet4
Cell Formulas
RangeFormula
N2:N11N2=SORT(UNIQUE(E2:E22))
O2:O11O2=LET(syscol,$E$2:$E$22,startcol,$G$2:$G$22,ResCol,$H$2:$H$22,sys,N2,mn,MINIFS(startcol,syscol,sys),mx,MAXIFS(ResCol,syscol,sys),s,SEQUENCE(mx-mn+1,,mn),SUM(SIGN(COUNTIFS(syscol,sys,startcol,"<="&s,ResCol,">="&s))))
Dynamic array formulas.


I had to change the dates to mm/dd/yy format, since that's the way my Excel is set up. It should not make a difference in the formula if your Excel is set up to use dd/mm/yy. But in the process of converting the dates, I might have converted a text value to a numeric (date) value. Check that your dates are actually dates.

Also, I noticed that many of the rows have a status of "open", and there is no resolution date. These rows will not be included in the final count, since resolution date will be considered "0", and won't be included in the COUNTIFS. That might be why you're getting values of 0. All of the systems that have at least one closed ticket show a value.
 
Upvote 0
That is strange then, do you think it matters that I am calculating the number of unique days against the serial number and not the system type? I figured it would just be a case of changing the ranges accordingly. Dates are definitely formatted as dates.

My main test system is GM0683 which I have input resolution dates on both entries in the log, the result is still showing as 0.
 
Upvote 0
That shouldn't matter. Here's what I get when I change it to look at serial number instead of system:

Book1
NO
1Serial NumberUnique Days
22010-05050
32016-07550
42020-08790
5GM00860
6GM037947
7GM04500
8GM0469161
9GM05170
10gm05220
11GM052871
12GM05360
13GM05640
14GM06400
15GM066390
16GM068326
17gs0990
18GS1050
19GS1470
Sheet4
Cell Formulas
RangeFormula
N2:N19N2=SORT(UNIQUE(D2:D22))
O2:O19O2=LET(syscol,$D$2:$D$22,startcol,$G$2:$G$22,ResCol,$H$2:$H$22,sys,N2,mn,MINIFS(startcol,syscol,sys),mx,MAXIFS(ResCol,syscol,sys),s,SEQUENCE(mx-mn+1,,mn),SUM(SIGN(COUNTIFS(syscol,sys,startcol,"<="&s,ResCol,">="&s))))
Dynamic array formulas.
 
Upvote 0
This gives the same result as Eric's:
Book1
NOPQR
1System TypeUnique DaysSerial NumberUnique Days
2CIX302010-05050
3Medical Software02016-07550
4RADiant 50Kv712020-08790
5RADiant 80Kv0GM00860
6RADiant Aura 50Kv26GM037947
7SARRP0GM04500
8X150161GM0469161
9X20090GM05170
10X3000gm05220
11GM052871
12GM05360
13GM05640
14GM06400
15GM066390
16GM068326
17gs0990
18GS1050
19GS1470
Sheet9
Cell Formulas
RangeFormula
N2:N10N2=SORT(TOCOL(UNIQUE(E2:E22),1))
O2:O10O2=BYROW(MAXIFS(H2:H22,E2:E22,N2#)-MINIFS(G2:G22,E2:E22,N2#)+1,LAMBDA(x,MAX(0,x)))
Q2:Q19Q2=SORT(TOCOL(UNIQUE(D2:D22),1))
R2:R19R2=BYROW(MAXIFS(H2:H22,D2:D22,Q2#)-MINIFS(G2:G22,D2:D22,Q2#)+1,LAMBDA(x,MAX(0,x)))
Dynamic array formulas.
 
Upvote 0
This is very strange then, I am still getting 0 from Eric' formula and I am getting 1 from Cubist' formula

This is Eric' formula, just edited to fit myactual ranges

Excel Formula: 
=LET(syscol,'Main Log'!D2:D2000,startcol,'Main Log'!G2:G2000,ResCol,'Main Log'!H2:H2000,sys,Dashboard!AU22,mn,MINIFS(startcol,syscol,sys),mx,MAXIFS(ResCol,syscol,sys),s,SEQUENCE(mx-mn+1,,mn),SUM(SIGN(COUNTIFS(syscol,sys,startcol,"<="&s,ResCol,">="&s))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
276
CaptainGravyBum
C
Z
  • Question Question
VBA: Date Format Issues with Data Validation
Replies
3
Views
399
zero269
Z
V
  • Question Question
Adjust formula to calculate monthly dynamic average of work hours
Replies
2
Views
57
vladimiratanasiu
V
E
  • Solved
Is there a way to ignore year in calculating duration of days?
Replies
3
Views
292
Joe4
Joe4
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
169
Sergius
S

Forum statistics

Threads
1,226,795
Messages
6,193,045
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top