Combining Overlapping Dates between Unique ID & Identify Overlapping with Other Date Range

I have a large spreadsheet in Google Sheet with headers with unique Machine Equipment IDs in A, start date in C, and end date in D. There are multiple rows per Machine Equipment ID and the start and end dates overlap. Why it cannot be overlap because one Machine can be rental by one client only. No possibilities on one Machine by two or more client in one time.

I need to find any gaps in the date ranges for each Machine Equipment ID.

Also, I need to calculate the utilization in this year only. From first January 2021 until today(23 Sept 2021) is 265 days.

Let say :

Machine EB27-45D was actually used in 2021 on

11 Jan 2021 to 10 June 2021 = 150 days

18 June 2021 to 1 July 2021 = 13 days

23 Aug 2021 to 1 Sept 2021 = 9 days

13 Sept 2021 to 13 Sept 2021 = 1 day

150 + 13 + 9 + 1 = 173 days

So for Machine ID EB27-45D utilization is (173/265)*100 = 65.28%

ZPeCE.png


I used the excel formula overlapping data range "First Jan until today" and "end date - start date" where cell R20 = MAX(MIN(Q20,D20)-MAX(P20,C20)+1,0)

But I realized there's another overlapping date between the same Machine Equipment ID



Elztb.png

this is a cross posting!

https://stackoverflow.com/questions/...-overlapping-w