CODEWITHSUNDEEP
excelvbaexcel-formula
TreeStone
TreeStone

Reputation: 11

Excel formula or VBA Identifying gaps in overlapping dates and times

I am attempting to find any gaps calculated in minutes between a start and stop date/time range. Essentially time when there are no appointments in the schedule, this is a 24hr service and I am looking for "dead" time when there isn't a customer in the office.

Currently I was attempting to use the =SUMPRODUCT((A2<B$2:B$19)*(B2>A$2:A$19))>1 to find overlaps and the issue I am running into is if there are any overlap in start or stop it disqualifies and does not truly identify the space between appointments just if that appointment is double booked at all.

Example image of worksheet

Upvotes: 1

Views: 452

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

Here is a new version of the Gap and Island solution to this problem, using Excel 365 functionality:

=LET(start,A2:A19,
end,B2:B19,
row,SEQUENCE(ROWS(start)),
maxSoFar,SCAN(0,row,LAMBDA(a,c,IF(c=1,INDEX(start,1),IF(INDEX(end,c-1)>a,INDEX(end,c-1),a)))),
SUM(IF(start>maxSoFar,start-maxSoFar,0)))

The algorithm is very simple:

 - Sort data by start time if necessary, then for each pair of times:
 -      Record the latest finish time so far (maxSoFar) (not including the present appointment)
 -      If the start time (start) is greater than maxSoFar, add start-maxSoFar to the total.

The first time interval is a special case - initialise maxSoFar to the first start time.

enter image description here

It can be seen that there are only two gaps in the appointments, from 4:15 to 7:31 (3 hours 16 minutes) and from 11:48 to 14:17 (3 hours 29 minutes) totalling 5 hours 45 minutes.

Why didn't I just use Max to make the code shorter? I don't know:

=LET(start,A2:A19,
end,B2:B19,
row,SEQUENCE(ROWS(start)),
maxSoFar,SCAN(0,row,LAMBDA(a,c,IF(c=1,INDEX(start,1),MAX(INDEX(end,c-1),a)))),
SUM(IF(start>maxSoFar,start-maxSoFar,0)))

Note

The precondition for this answer is that the data have to be sorted in ascending order of start time. If this isn't the case, you could add a sort:

=LET(start,SORT(A2:A19),
    end,SORTBY(B2:B19,A2:A19),
    row,SEQUENCE(ROWS(start)),
    maxSoFar,SCAN(0,row,LAMBDA(a,c,IF(c=1,INDEX(start,1),MAX(INDEX(end,c-1),a)))),
    SUM(IF(start>maxSoFar,start-maxSoFar,0)))

Upvotes: 2

Ece
Ece

Reputation: 20

To find the gaps between appointments in a schedule, you can try using the following formula:

=SUM(B2:B19)-SUMPRODUCT((A2<B$2:B$19)*(B2>A$2:A$19))

You can then convert the duration to minutes by multiplying the result by 1440 (the number of minutes in a day).

=1440*(SUM(B2:B19)-SUMPRODUCT((A2<B$2:B$19)*(B2>A$2:A$19)))

Upvotes: 0

Related Questions