subtracting multi-valued times in one cell

[Guest]

I use a program that exports times into an excel document to allow additional analysis. It exports X number of start times for the first group with each value on its own line and all in one cell. X number of ending times are placed in the next cell in the same manner, each on its own line. The next row has start and end times for however many items were in that group, and continues with each row having all of the times for one group. Is there a way to calculate the duration between start and end times for each of the values in a cell

A B
--------------------------------------
04:25:30 | 04:27:35 |
04:26:30 | 04:28:17 |
04:31:06 | 04:32:48 |
--------------------------------------
15:38:22 | 15:52:51 |
15:33:04 | 15:37:42 |
--------------------------------------
Here is what I came up with, but it returns the decimal value for the time, and can not automatically account for the number of items in each group. Also, the three separate lines are required to have the output formatting match the input formatting. Surely there is a better way to handle this. Thank you for your help

=CONCATENATE(MID(B1,1,8)-MID(A1,1,8),
",MID(B1,10,8)-MID(A1,10,8),
",MID(B1,19,8)-MID(A1,19,8))

 

[Bob Phillips]

Scott,

In C1 just input
=B1-A1
and format as time.

Time is just a serial number with a particular format for presentation, so
normal math can be performed on time cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I use a program that exports times into an excel document to allow

additional analysis. It exports X number of start times for the first group
with each value on its own line and all in one cell. X number of ending
times are placed in the next cell in the same manner, each on its own line.
The next row has start and end times for however many items were in that
group, and continues with each row having all of the times for one group.
Is there a way to calculate the duration between start and end times for
each of the values in a cell?

A B C

time, and can not automatically account for the number of items in each
group. Also, the three separate lines are required to have the output
formatting match the input formatting. Surely there is a better way to
handle this. Thank you for your help.

 

[Leo Heuser]

Scott

This subroutine will do the hard work, while
you sit back and relax (you have to relax
fast

All times are distributed to their own cells and
column C may then contain =B1-A1 etc.

The only "problem" is, which delimiter has been
used in your data. I have used character 10,
which is used in Excel, but if it doesn't work on
your data, you can try

Delim = Chr(13)
or
Delim = Chr(11)

If neither of these work, you are welcome to
mail an attachment to me, and I'll give it a try.
By setting

MaxNumberOfColumns = 2

to another number (from 1 and up), you can
have as many columns converted as you like
with one blow.

To use the routine, enter the VBA editor with
<Alt><F11>, click the project in the project
window (the upper left of the screen), and
choose the menu Insert > Module.
Copy the below code and paste it to the right-
hand window. Return to the sheet with <Alt><F11>
and save the workbook.
From the sheet in question choose the routine
with Tools > Macro > Macros.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.


Sub MoreTimesInCell()
'Leo Heuser, 9 Feb. 2004
Dim AnswerRange As Range
Dim Area As Range
Dim Cell As Range
Dim CellArray() As Variant
Dim CheckRange As Range
Dim CheckRangeValue As Variant
Dim Counter As Long
Dim Counter1 As Long
Dim Delim As String
Dim DestRange As Range
Dim Dummy As Variant
Dim Element As Long
Dim MaxNumberOfColumns As Long
Dim NewArray() As Variant
Dim NewBound As Long
Dim TitleText As String

On Error GoTo Finito

Delim = Chr(10)
MaxNumberOfColumns = 2

TitleText = "Please select one cell in each column." & vbNewLine
TitleText = TitleText & "(Drag or use <Ctrl>)" & vbNewLine
TitleText = TitleText & "No blanks allowed inside ranges." & vbNewLine
TitleText = TitleText & "Max. " & MaxNumberOfColumns & " columns."

Set AnswerRange = Application.InputBox(TitleText, Type:=8)

If AnswerRange.Cells.Count > MaxNumberOfColumns Then GoTo Finito

For Each Area In AnswerRange.Areas
For Each Cell In Area.Cells
Set CheckRange = Range(Cells(Cell.End(xlUp).Row, _
Cell.Column), _
Cells(Cell.End(xlDown).Row, Cell.Column))
CheckRangeValue = CheckRange.Value

Dummy = UBound(CheckRangeValue, 1)
NewBound = 0
Element = 0

ReDim CellArray(1 To Dummy, 1 To 1)

For Counter = 1 To Dummy
CellArray(Counter, 1) = _
Split(CheckRangeValue(Counter, 1), Delim)
NewBound = NewBound + UBound(CellArray(Counter, 1)) + 1
Next Counter

ReDim NewArray(1 To NewBound, 1 To 1)

For Counter = 1 To Dummy
For Counter1 = 0 To UBound(CellArray(Counter, 1))
Element = Element + 1
NewArray(Element, 1) = CellArray(Counter, 1)(Counter1)
Next Counter1
Next Counter

Set DestRange = CheckRange.Resize(UBound(NewArray, 1), 1)

With DestRange
.NumberFormat = "hh:mm:ss"
.Value = NewArray
.EntireRow.AutoFit
End With
Next Cell
Next Area

Finito:
On Error GoTo 0
End Sub

I use a program that exports times into an excel document to allow

additional analysis. It exports X number of start times for the first group
with each value on its own line and all in one cell. X number of ending
times are placed in the next cell in the same manner, each on its own line.
The next row has start and end times for however many items were in that
group, and continues with each row having all of the times for one group.
Is there a way to calculate the duration between start and end times for
each of the values in a cell?

A B C

time, and can not automatically account for the number of items in each
group. Also, the three separate lines are required to have the output
formatting match the input formatting. Surely there is a better way to
handle this. Thank you for your help.