Finding Shortest Time Span within a Time Range.

Y

yessir

Board Regular
Joined
Jun 7, 2019
Messages
103
Office Version
  1. 2021
Platform
  1. MacOS
I've got a question in regards to a formula that I'm having trouble solving. Is there a way to find the times with the shortest time between them and then be able to calculate the time inbetween said times. I would normally not have trouble doing this (when the times are in different cells) However, in this case the times all appear in the same cell. Any help is appreciated!

(Below is an example of what I have (A1) and what I wish for it to produce (J1)
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]A1
[/TD]
[TD][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]J1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 6"]10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]10:32-11:02, 15:59-16:26, 22:07-6:05
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]27
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"]8:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
You could use a UDF that splits the string into time ranges and compares the differences in minutes.

Code: 
Function MinTime(str As String) As Long
Dim aTimes As Variant
Dim arr As Variant
Dim v As Variant
Dim iDiff&
Application.Volatile


aTimes = Split(str, ",")
For Each v In aTimes
    arr = Split(Trim(v), "-")
    If CDate(arr(0)) > CDate(arr(1)) Then arr(1) = CDate(arr(1)) + 1
    If iDiff = 0 Then
        iDiff = DateDiff("n", CDate(arr(0)), CDate(arr(1)))
    Else
        iDiff = WorksheetFunction.Min(iDiff, DateDiff("n", CDate(arr(0)), CDate(arr(1))))
    End If
Next v
MinTime = iDiff
End Function

In cell J1, you would use: =MinTime(A1)
 
Last edited:
Upvote 0
Here's a UDF you can try as in the example below.
Code: 
Function yessir(S As String) As Double
Dim V As Variant, E As Date, L As Date, i As Long, M As Double
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    E = Left(V(i), InStr(V(i), "-") - 1)
    L = Right(V(i), Len(V(i)) - InStr(V(i), "-"))
    If i = LBound(V) Then
        M = ((L - E) - Int(L - E)) * 24 * 60
    Else
        M = Application.Min(M, ((L - E) - Int(L - E)) * 24 * 60)
    End If
Next i
yessir = M
End Function
Excel Workbook
AB
110:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:0024
210:32-11:02, 15:59-16:26, 22:07-6:0527
38:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:3525
Sheet11
 
Upvote 0
Thanks so much for your reply! Just a follow up quesiton, what would be the UDF if I wanted to do that same thing but find the max times? Would it also be possible to highlight the time range within the cell that the Max and Min Times are coming from?
 
Upvote 0
yessir said:
Thanks so much for your reply! Just a follow up quesiton, what would be the UDF if I wanted to do that same thing but find the max times? Would it also be possible to highlight the time range within the cell that the Max and Min Times are coming from?
Click to expand...
You had two responses to your initial post. To whom are you addressing this post?
 
Upvote 0
yessir said:
To either of you! As you both have been very helpful
Click to expand...
For the code I posted, to find the max time range just replace this line:
Code: 
M = Application.Min(M, ((L - E) - Int(L - E)) * 24 * 60)
with this:
Code: 
M = Application.Max(M, ((L - E) - Int(L - E)) * 24 * 60)
 
Upvote 0
JoeMo said:
For the code I posted, to find the max time range just replace this line:
Code: 
M = Application.Min(M, ((L - E) - Int(L - E)) * 24 * 60)
with this:
Code: 
M = Application.Max(M, ((L - E) - Int(L - E)) * 24 * 60)
Click to expand...


Thanks so much for your help Joe! I just had another quick question for you in regards to the different times. Is there by chance a formula that would be able to sum up the total time in between the time ranges? For example.

[TABLE="width: 443"]
<colgroup><col width="527" style="width: 395pt; mso-width-source: userset; mso-width-alt: 19273;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 527, bgcolor: transparent"]A1 (Times)[/TD]
[TD="width: 64, bgcolor: transparent"]B1 (Min)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00[/TD]
[TD="bgcolor: transparent, align: right"]444[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10:32-11:02, 15:59-16:26, 22:07-6:05[/TD]
[TD="bgcolor: transparent, align: right"]535[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35[/TD]
[TD="bgcolor: transparent, align: right"]541[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
yessir said:
Thanks so much for your help Joe! I just had another quick question for you in regards to the different times. Is there by chance a formula that would be able to sum up the total time in between the time ranges? For example.

[TABLE="width: 443"]
[TR]
[TD="width: 527, bgcolor: transparent"]A1 (Times)[/TD]
[TD="width: 64, bgcolor: transparent"]B1 (Min)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00[/TD]
[TD="bgcolor: transparent, align: right"]444[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10:32-11:02, 15:59-16:26, 22:07-6:05[/TD]
[TD="bgcolor: transparent, align: right"]535[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35[/TD]
[TD="bgcolor: transparent, align: right"]541[/TD]
[/TR]
[/TABLE]
Click to expand...
You are welcome. As for your quick question, here's a companion UDF to do what you have requested, as in example below:
Excel Workbook
AB
110:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00444
210:32-11:02, 15:59-16:26, 22:07-6:05535
38:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35541
Sheet10



Code: 
Function yessirSum(S As String) As Double
Dim V As Variant, E As Date, L As Date, i As Long
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    E = Left(V(i), InStr(V(i), "-") - 1)
    L = Right(V(i), Len(V(i)) - InStr(V(i), "-"))
    yessirSum = yessirSum + ((L - E) - Int(L - E)) * 24 * 60
Next i
End Function
 
Upvote 0
JoeMo said:
You are welcome. As for your quick question, here's a companion UDF to do what you have requested, as in example below:
Sheet10

A
B
10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00
10:32-11:02, 15:59-16:26, 22:07-6:05
8:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]444
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]535
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]541
[/TD]

</tbody>

Spreadsheet Formulas

Cell
Formula
B1
=yessirSum(A1)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Code: 
Function yessirSum(S As String) As Double
Dim V As Variant, E As Date, L As Date, i As Long
V = Split(S, ", ")
For i = LBound(V) To UBound(V)
    E = Left(V(i), InStr(V(i), "-") - 1)
    L = Right(V(i), Len(V(i)) - InStr(V(i), "-"))
    yessirSum = yessirSum + ((L - E) - Int(L - E)) * 24 * 60
Next i
End Function
Click to expand...

[TABLE="width: 483"]
<tbody>[TR]
[TD="width: 644, bgcolor: transparent"]
Thanks for your help Joe, by chance do you know if there is a way to use conditional formatting that would be able to highlight the shortest time span between the ranges in green and the longest time span between ranges in red? Just wondering as I've been playing around with it for a bit!

[TABLE="width: 395"]
<tbody>[TR]
[TD="width: 527, bgcolor: transparent"]10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10:32-11:02, 15:59-16:26, 22:07-6:05
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35(<=Red)

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
164
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
S
  • Solved
Individual Ranking
2
Replies
11
Views
433
sksanjeev786
S
M
  • Question Question
SLA Time Calculation
Replies
5
Views
306
felixstraube
F
T
  • Solved
Determining a Range within a Column using VBA
Replies
1
Views
84
jolivanes
jolivanes

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top