Excel formula or VBA Identifying gaps in overlapping dates and times-CodePudding

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

CodePudding user response：

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)))

CodePudding user response：

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.

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)))