Finding Shortest Time Span within a Time Range.

[yessir]

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)

A1									J1
10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00				25
10:32-11:02, 15:59-16:26, 22:07-6:05						27
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		25

<tbody>
</tbody>

 

[CalcSux78]

You could use a UDF that splits the string into time ranges and compares the differences in minutes.

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)

 

[JoeMo]

Here's a UDF you can try as in the example below.

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
	A	B
1	10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00	24
2	10:32-11:02, 15:59-16:26, 22:07-6:05	27
3	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	25
Sheet11

 

[yessir]

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?

 

[JoeMo]

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?

You had two responses to your initial post. To whom are you addressing this post?

 

[yessir]

You had two responses to your initial post. To whom are you addressing this post?

To either of you! As you both have been very helpful

 

[JoeMo]

To either of you! As you both have been very helpful

For the code I posted, to find the max time range just replace this line:

M = Application.Min(M, ((L - E) - Int(L - E)) * 24 * 60)

with this:

M = Application.Max(M, ((L - E) - Int(L - E)) * 24 * 60)

 

[yessir]

For the code I posted, to find the max time range just replace this line:

M = Application.Min(M, ((L - E) - Int(L - E)) * 24 * 60)

with this:

M = Application.Max(M, ((L - E) - Int(L - E)) * 24 * 60)

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.

A1 (Times)	B1 (Min)
10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00	444
10:32-11:02, 15:59-16:26, 22:07-6:05	535
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	541

<colgroup><col width="527" style="width: 395pt; mso-width-source: userset; mso-width-alt: 19273;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[JoeMo]

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.

A1 (Times)	B1 (Min)
10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00	444
10:32-11:02, 15:59-16:26, 22:07-6:05	535
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	541

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
	A	B
1	10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00	444
2	10:32-11:02, 15:59-16:26, 22:07-6:05	535
3	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	541
Sheet10

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

 

[yessir]

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
1	10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00	444
2	10:32-11:02, 15:59-16:26, 22:07-6:05	535
3	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	541

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =yessirSum(A1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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

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! 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(<=Red) <tbody> </tbody>

<tbody>
</tbody>