Overlapping Record Count(s) Based on Start Date/Time and End Date/Time

[Blanche]

I stumbled upon this thread trying to find a solution to replace a canned report + manual process with a clean MS Excel spreadsheet. I LOVE the solution provided, but my OCD couldn't stop me from comparing the new process results with the old. The counts are so close it makes me question my previous process or the new formula. I've looked and I couldn't find the records which caused the count difference. At this point I feel like I'm looking for a needle in a haystack and was hoping someone had an easier process to see why/where my counts are off.

Previously a report would run for the previous month. I would generate a formula to remove the duplicates. The report gives Record-A that overlaps with Record-B, and also that Record-B overlaps with Record-A, this is manual process 1. Then I would take the resulting not-duplicated records, copy them to a new sheet and perform counts, generate a pivot table and then perform percentages based on total records, this is manual process 2.

The new process is much cleaner; I'm hoping that the issue is with my previous process.

I have attached the spreadsheet which shows last month's records using the old/new format. I greatly appreciate your time and assistance.

 

[vletm]

Have You compared Your results and data?
I tried to do this 'my-way' ... and I got something else than You.
You can see my values.
There is a chart, which could give some ideas too.
It's still very rough sample.

 

[Blanche]

WOW, your results are very different! Unfortunately, my unit automatically disables macros and I was hoping to find a solution that doesn't use them. Thank you for sharing!

 

[vletm]

Again ... Did You compare Your results with Your data?
... or do Your unit will use those Your results somewhere?

... or did You get an idea - why those results are ... different?

I continued testing ... with my own modified file.
If select eg Record No 38032,
it will show something like below based times:
Green - selected record
Black - overlapped
Yellow - other simultaneous records
Orange - overlaps within those 'yellow' records

There could be possible to get something like ... to see the whole datas 'workload'.

 

[Blanche]

That is what I still need to do, especially reviewing your example. Thank you very much for your time and effort on this project, I really appreciate it.

 

[vletm]

Still testing ... my found MAX workload seems to be nine (9) ... max nine records in same time.

Which values someone is interesting ... simultaneous or 'workload'?
I found one view to see 'workload' per time - right side of below snapshot

Above part shows records per time - green is selected
Below part shows 'workload' - colors & height shows 'workload's ... there are nine (9) records same time in the end of 38032.
Left side shows that there are 13 simultaneous records with 38032.
Which one value (9 or 13) would match with Your needs?