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

 

[JoeMo]

[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]

You are welcome.

Conditional formatting changes the format of an entire cell that meets the condition(s), not parts of it.

 

[yessir]

You are welcome.

Conditional formatting changes the format of an entire cell that meets the condition(s), not parts of it.

Understood!!! Thanks for your insight Joe!

 

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

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 588px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , 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

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

Hello,

I actually am having a bit of trouble with the formula where it retunrs a VALUE error when it tries to read a cell containing times like this.

[TABLE="width: 465"]
<colgroup><col width="620" style="width: 465pt; mso-width-source: userset; mso-width-alt: 22674;"> <tbody>[TR]
[TD="width: 620, bgcolor: transparent"]17:51-17:53, 18:35

Ideally with the max formula I would want it to read 2 and for the min formula it should read 1, but it is currently returing the VALUE error. Thanks.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Hello,

I actually am having a bit of trouble with the formula where it retunrs a VALUE error when it tries to read a cell containing times like this.

[TABLE="width: 465"]
<tbody>[TR]
[TD="width: 620, bgcolor: transparent"]17:51-17:53, 18:35

Ideally with the max formula I would want it to read 2 and for the min formula it should read 1, but it is currently returing the VALUE error. Thanks.
[/TD]
[/TR]
</tbody>[/TABLE]

The function you posted is designed to produce the sum of the time spans in a cell, not the min or max span.In any case each start time must be accompanied by an end time, for example: 17:51-17:53. The data you show includes a single time (18:35) not a span. That produces the #VALUE ! because you are missing the companion end time to 18:35.

 

[yessir]

The function you posted is designed to produce the sum of the time spans in a cell, not the min or max span.In any case each start time must be accompanied by an end time, for example: 17:51-17:53. The data you show includes a single time (18:35) not a span. That produces the #VALUE ! because you are missing the companion end time to 18:35.

Is it possible to include that as one minute? As for the minute at 18:35 is important within the data set. Just wondering. Thanks.

 

[JoeMo]

Is it possible to include that as one minute? As for the minute at 18:35 is important within the data set. Just wondering. Thanks.

If you use the min function from post #3 and make that 18:35-18:35, the function will return 0, not 1. You would need 18:35-18:36 for it to return a 1. What's the point of having a single time when you're asking to find the min or max of a series of time spans?

 

[yessir]

If you use the min function from post #3 and make that 18:35-18:35, the function will return 0, not 1. You would need 18:35-18:36 for it to return a 1. What's the point of having a single time when you're asking to find the min or max of a series of time spans?

11:26-11:58, 12:02, 13:05-13:24, 16:48-17:17, 18:53-18:58

My apologies, I must not be explaning this correctly. As we can see in this time range there is one value that is only 12:02. Whenever a data set is like this and I go to use either minimum time span formula or the max time span formula they both return the VALUE error. I have only noticed this error on time sets where this single value time occurs and was wondering if there was a way I could change the formula to pull the correct times. Hope this helps. Thanks.

 

[JoeMo]

11:26-11:58, 12:02, 13:05-13:24, 16:48-17:17, 18:53-18:58

My apologies, I must not be explaning this correctly. As we can see in this time range there is one value that is only 12:02. Whenever a data set is like this and I go to use either minimum time span formula or the max time span formula they both return the VALUE error. I have only noticed this error on time sets where this single value time occurs and was wondering if there was a way I could change the formula to pull the correct times. Hope this helps. Thanks.

Do you understand that the functions are designed to compare time spans and return the min or max span? The functions do not comprehend a single time within a string of time spans. And, I ask again, what's the logic behind including a single time among a string of time spans??

I don't understand what the "correct times" are when there is a single time among a string of time spans.