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".

 

[MichelleH77]

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".

Hi Rijnsent,

Option 3 was great as it showed me which vehicles were with us at the exact same time so thank you for that. Building on this (if allowed), is there a way to show the actual overlaps in time? E.g. in the example we're using, if Vehicle 4 arrived during the times that Vehicles 2 and 3 were already with us (say the timestamp for Vehicle 4's arrival was 03/07/2024 18:00, is there a formula that would tell me Vehicle 2 and Vehicle 3 had 7 hours occurring simultaneously, but Vehicle 4 had 2 hours overlapping with both Vehicles 2 and 3? Appreciate if this is a completely different question that I need to post on a new thread!

Many thanks,

Michelle

 

[Rijnsent]

Hi Michelle,
that does sound possible. So in that example your data would show for Vehicle 4 (say in the column next to the time slots): "Vehicle 2 - 2h, Vehicle 3 - 2h" and for Vehicle 2 it would show "Vehicle 3 - 7h, Vehicle 4 - 2h"? The formula for that would get quite complex, I think you'd need a LET function for it.

I did find that my 3rd function is missing the check on the end time, this filter adds that: (put this formula in e.g. F2 and drag down):

=TEXTJOIN(",",TRUE,IFERROR(FILTER(DATA!$A$2:$A$12,((DATA!$B$2:$B$12<C2)*(DATA!$C$2:$C$12>=C2)+(DATA!$B$2:$B$12<=B2)*(DATA!$C$2:$C$12>B2))*($A$2:$A$12<>A2)),""))

Note here: the * in the filter indicates it's an AND statement, the + is an OR statement.

For the overlapping hours LET formula, this is the start, but it needs some building and testing for which I have no time at the moment. This site has some good explainers: LET Function

=LET(data,A2:C12,
v,A2,
s,B2,
e,C2,
vehicles,CHOOSECOLS(data,1),
starttimes,CHOOSECOLS(data,2),
endtimes,CHOOSECOLS(data,3),
'filter and calculate overlapping number of hours
'TEXTJOIN results together
... etc.

 

[Rijnsent]

I had some time, this formula works on my end. It can probably be made prettier, but it does the job. Put this in G2 and drag down . It is way too complicated for my liking. What you see happening in the LET function is basically almost programming: first there is a variable called "data", which holds your data block, after that there are 3 variables of the row that you're looking at (named v, s and e for vehicle, starting time and end time) and with those elements you do several steps of calculations, leading to a small table with a column with "overlappingVehicles" and the calculated "overlappingHours".
You can try out some elements yourself: if you type =CHOOSECOLS($A$2:$C$12;2) you can e.g. see what the variable "starttimes" holds.

=TEXTJOIN(" - ",TRUE,IFERROR(LET(
    data, $A$2:$C$12,
    v, A2,
    s, B2,
    e, C2,
    vehicles, CHOOSECOLS(data, 1),
    starttimes, CHOOSECOLS(data, 2),
    endtimes, CHOOSECOLS(data, 3),

    overlapHours, IF((IF(e < endtimes, e, endtimes) - IF(s > starttimes, s, starttimes)) > 0,
                     (IF(e < endtimes, e, endtimes) - IF(s > starttimes, s, starttimes)) * 24,
                     0),
    overlappingVehicles, FILTER(vehicles, (overlapHours > 0) * (vehicles <> v)),
    overlappingHours, ROUND(FILTER(overlapHours, (overlapHours > 0) * (vehicles <> v)),1),

    HSTACK(overlappingVehicles, overlappingHours)
),""))

 

[MichelleH77]

I had some time, this formula works on my end. It can probably be made prettier, but it does the job. Put this in G2 and drag down . It is way too complicated for my liking. What you see happening in the LET function is basically almost programming: first there is a variable called "data", which holds your data block, after that there are 3 variables of the row that you're looking at (named v, s and e for vehicle, starting time and end time) and with those elements you do several steps of calculations, leading to a small table with a column with "overlappingVehicles" and the calculated "overlappingHours".
You can try out some elements yourself: if you type =CHOOSECOLS($A$2:$C$12;2) you can e.g. see what the variable "starttimes" holds.

=TEXTJOIN(" - ",TRUE,IFERROR(LET(
    data, $A$2:$C$12,
    v, A2,
    s, B2,
    e, C2,
    vehicles, CHOOSECOLS(data, 1),
    starttimes, CHOOSECOLS(data, 2),
    endtimes, CHOOSECOLS(data, 3),

    overlapHours, IF((IF(e < endtimes, e, endtimes) - IF(s > starttimes, s, starttimes)) > 0,
                     (IF(e < endtimes, e, endtimes) - IF(s > starttimes, s, starttimes)) * 24,
                     0),
    overlappingVehicles, FILTER(vehicles, (overlapHours > 0) * (vehicles <> v)),
    overlappingHours, ROUND(FILTER(overlapHours, (overlapHours > 0) * (vehicles <> v)),1),

    HSTACK(overlappingVehicles, overlappingHours)
),""))

This is brilliant! It definitely gives me what I was hoping for in terms of identifying overlaps. I have some more work to do in terms of identifying how to split the charges once I have these shared hours, but this is a great starting point. Thanks so much for your help!