Calculate number of non-overlapping days from multiple start/end date ranges

[mentalvelcro]

Hello all - never posted in a forum before. I've been searching the internet for a solution, but can't find one so I hope I describe this properly.

I have a list of multiple date ranges in excel (Office 365).

Column A is the start date, column B is the end date.

Some of these date ranges overlap one another.

I want to calculate the total number of non-overlapped days from the earliest start date in the list to the latest end date in the list.

[soledad]

Hi there
Suppose that your days list is in A1:A9 and B1:B9 ranges
You can count the total number of non-overlapped days as follows:

Please Login or Register  to view this content.

End it with CTRL+SHIFT+ENTER combination
Hope this heps

[hrlngrv]

First specs. If start date were 30 Oct 2020 and end date were 1 Nov 2020, would that be 2 or 3 days? I'll assume 3.

There's the efficient way using multiple cells, and the clever way using a single formula.

Efficient: if your start dates were in A2:A25 and end dates in B2:B25, and the table were sorted by col A in ascending order, add alternative start dates in C2:C25.

C2: =A2-1
C3: =IF(COUNTIF(B$2:B2,">="&A3),MAX(B$2:B2),A3-1)

Fill C3 down into C4:C25. Non-overlapping days would be given by =SUM(B2:B25-C2:C25).

Clever: this uses the latest functions.

=LET(x,SEQUENCE(1,MAX(B2:B25)-MIN(A2:A25)+1,MIN(A2:A25),1),COUNT(1/MMULT(SEQUENCE(1,ROWS(A2:A25),1,0),(x>=A2:A25)*(x<=B2:B25))))