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

1. ## 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

2. ## Re: Combining Overlapping Dates between Unique ID & Identify Overlapping with Other Date R

Hello bihaequipinc and Welcome to Excel Forum.
I may be easier to help if we could see an Excel sample of the file from which the screenshots in post #1 were produced.
Instructions for uploading an Excel file are given in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page.
Let us know if you have any questions.

3. ## Re: Combining Overlapping Dates between Unique ID & Identify Overlapping with Other Date R

this is a cross posting!

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

4. ## Re: Combining Overlapping Dates between Unique ID & Identify Overlapping with Other Date R

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new I will provide it for you this time: https://stackoverflow.com/questions/...-overlapping-w)

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1