1

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

braX
  • 11,506
  • 5
  • 20
  • 33
TreeStone
  • 11
  • 2
  • 1
    I've had a couple of shots at this in the past using Gap and Island https://stackoverflow.com/questions/66693871/finding-time-spent-in-continuous-data/66720385#66720385 and https://stackoverflow.com/questions/53572815/duration-and-idle-time-for-a-server-from-continuous-dates/53579235#53579235 However rather than label it as a duplicate, it might be an opportunity to revisit it using sort, scan etc. in Excel 365. – Tom Sharpe Jan 05 '23 at 09:14
  • 1
    BTW it looks as though the data are already in order of registration times - is this correct? Is there any chance of getting the data as text that we can copy and paste rather than an image? – Tom Sharpe Jan 05 '23 at 09:17

2 Answers2

1

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)))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

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)))
Ece
  • 20
  • 3
  • Thank you for the response, because I am working with date ranges the return for this formula is pulling back a date and time. I may be miss understanding but it does not seem to be working. – TreeStone Jan 04 '23 at 20:19
  • I apologize I forgot the @Ece Thank you for the response, because I am working with date ranges the return for this formula is pulling back a date and time. I may be miss understanding but it does not seem to be working. – TreeStone Jan 04 '23 at 20:27
  • sorry, this should be working: =MIN(B2:B19)-MAX(A2:A19) then you can convert the duration to minutes by dividing the result by the number of minutes in a day (1440). – Ece Jan 04 '23 at 20:27
  • if it didn't work for your problem, I probably misunderstand it. I'm sorry. – Ece Jan 04 '23 at 20:33
  • no worries this is my fault if you misunderstood. I have overlapping dates and I am looking for anytime there is no overlap or the space between the last stop date and the next start date. – TreeStone Jan 04 '23 at 20:38