Find newest Time and oldest Time from a cell with many times separated by a comma and subtract them

[learningstatistics]

Hello,

I have cells that looks something like what is shown in column 1. I want to tell excel "Find the newest time (ie 12:07:35) and the oldest time (ie 12:07:33). So two formulas here. Then I want to subtract them.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 263"]
<colgroup><col></colgroup><tbody>[TR]
[TD]12:07:33,12:07:33,12:07:35,12:07:35[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:25:55,15:25:55,15:25:55,15:25:55
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:37:58[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Some of the cells have only one time like the one in column 1 row 3. If this is the case, I want excel to subtract this cell from itself. So, the answer would always be zero.


I appreciate any help.

Thanks

 

[DRSteele]

I suggest you conduct a Text-to-Columns operation on the column. Then compose a formula that finds the MAX and MIN values in each row and subtracts one from the other. Perhaps you could even use PowerQuery to perform all the heavy lifting.

Attempting to use formulas to separate text strings by delimiters (delimiters of varying frequencies especially) makes for long days and causes migraines. Note that there are dozens of threads in this forum that achieve the objective with VBA code.