Timestamps - finding & measuring overlaps

[MichelleH77]

Hi, I have a set of data organised in 3 columns (Vehicle, Timestamp1 and Timestamp2).

I need to try and find the common values (i.e. where date/time of the entries overlap and by how much).

My simplified sheet (image attached as I can't use the XL2BB here), shows the comings and goings of vehicles and I need to try and show which were in our location at the same time for each timestamp shown e.g. Vehicle 1 had 6 hours in the same location as Vehicle 2 on X date. I've tried all sorts of things but just can't quite get to where I need to be, and am not actually sure whether it's even possible. If necessary the date and time can be split out into separate columns.

Many thanks,

Michelle

 

[Rijnsent]

Hi Michelle, welcome to the forum. So what would your desired output & input be?

Some ideas, on a new sheet, put a date & time in cell B2 (I named your sheet with data DATA), than:
C2, this counts the number of vehicles on that timestamp, the end-time is excluded:

=COUNTIFS(DATA!$B$2:$B$12,"<="&B2,DATA!$C$2:$C$12,">"&B2)

D2, this gives a list of vehicles present at that time (same conditions), this is quite a new Excel function:

=FILTER(DATA!$A$2:$A$12,(DATA!$B$2:$B$12<=B2)*(DATA!$C$2:$C$12>B2)

E2: the list of D2, but in one cell:

=TEXTJOIN(",",TRUE,FILTER(DATA!$A$2:$A$12,(DATA!$B$2:$B$12<=B2)*(DATA!$C$2:$C$12>B2),"NO RESULTS"))

If I enter e.g. 03-07-2024 15:00:00 in that field, the results are 2 vehicles, "Vehicle 2,Vehicle 3".