I used autofilter to remove rows with column C being a blank

'assume data ime in column A and event in column B
Sub splittimes()

With Sheets("Sheet2")
.Range("A1") = "Task"
.Range("B1") = "Start"
.Range("C1") = "End"
.Range("D1") = "Elapse Time"
NewRow = 2
End With

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
TaskTime = .Range("A" & RowCount)
TaskName = .Range("B" & RowCount)
'remove start or end from task name
Task = Trim(Left(TaskName, InStr(TaskName, "-") - 1))
'get start or end
TaskEvent = Trim(Mid(TaskName, InStr(TaskName, "-") + 1))
'search for event
With Sheets("Sheet2")
Set c = .Columns("A").Find(what:=Task, _
LookIn:=xlValues, lookat:=xlWhole, _
searchdirection:=xlPrevious)
If c Is Nothing Then
'add to end of worksheet
.Range("A" & NewRow) = Task
Select Case TaskEvent

Case "START"
.Range("B" & NewRow) = TaskTime
Case "END"
.Range("C" & NewRow) = TaskTime
End Select

NewRow = NewRow + 1
Else

Select Case TaskEvent
Case "START"
'check if last event had a start time
If c.Offset(0, 1) = "" Then
c.Offset(0, 1) = TaskTime
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("B" & NewRow) = TaskTime
NewRow = NewRow + 1
End If
Case "END"
'check if last event had an end time
If c.Offset(0, 2) = "" Then
c.Offset(0, 2) = TaskTime
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("C" & NewRow) = TaskTime
NewRow = NewRow + 1
End If
End Select

End If

End With
Next RowCount
End With

With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'add formula to column D
.Range("D2").Formula = "=C2-B2"
'copy formula down worksheet
.Range("D2").Copy _
Destination:=.Range("D2:D" & LastRow)
.Columns("D").NumberFormat = "[H]:mm:ss"
'find rows with no end time
Set c = .Range("C1:C" & LastRow).Find(what:="", _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
'autofilter columns c searching for blanks
.Columns("C:C").AutoFilter
.Columns("C:C").AutoFilter Field:=1, Criteria1:="="
'delete visible rows
.Rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Delete
'remove autofilter
.Columns("C:C").AutoFilter
End If
End With




End Sub


"Mohan" wrote:

Thank you

It works wonderfull. One more help

What do I have to do if I want to skip inserting a row in sheet2 if there is
no end time for a task?

"Joel" wrote:

there was a case I didn't consider. If you start taking data after a Task
start you would only havean end time for that task. the next Start timne for
the task would be put in the same results row. You would then have the Start
time for the task after the End time. Weird results. The code below fixes
this problem.


'assume data ime in column A and event in column B
Sub splittimes()

With Sheets("Sheet2")
.Range("A1") = "Task"
.Range("B1") = "Start"
.Range("C1") = "End"
.Range("D1") = "Elapse Time"
NewRow = 2
End With

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
TaskTime = .Range("A" & RowCount)
TaskName = .Range("B" & RowCount)
'remove start or end from task name
Task = Trim(Left(TaskName, InStr(TaskName, "-") - 1))
'get start or end
TaskEvent = Trim(Mid(TaskName, InStr(TaskName, "-") + 1))
'search for event
With Sheets("Sheet2")
Set c = .Columns("A").Find(what:=Task, _
LookIn:=xlValues, lookat:=xlWhole, _
searchdirection:=xlPrevious)
If c Is Nothing Then
'add to end of worksheet
.Range("A" & NewRow) = Task
Select Case TaskEvent

Case "START"
.Range("B" & NewRow) = TaskTime
Case "END"
.Range("C" & NewRow) = TaskTime
End Select

NewRow = NewRow + 1
Else

Select Case TaskEvent
Case "START"
'check if last event had a start time
If c.Offset(0, 1) = "" Then
'check if start time is before end time
'Or there is no end time
if c.offset(0,2) = "" or _
TaskTime < c.offset(0,2) then

c.Offset(0, 1) = TaskTime
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("B" & NewRow) = TaskTime
NewRow = NewRow + 1
End if
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("B" & NewRow) = TaskTime
NewRow = NewRow + 1
End If
Case "END"
'check if last event had an end time
If c.Offset(0, 2) = "" Then
c.Offset(0, 2) = TaskTime
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("C" & NewRow) = TaskTime
NewRow = NewRow + 1
End If
End Select

End If

End With
Next RowCount
End With

With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'add formula to column D
.Range("D2").Formula = "=C2-B2"
'copy formula down worksheet
.Range("D2").Copy _
Destination:=.Range("D2:D" & LastRow)
.Columns("D").NumberFormat = "[H]:mm:ss"
End With

End Sub



"Joel" wrote:

THIS IS NOT A CHALLENGE. I assumed that the data and time where in column A
and the task name was in column B. the code assumes the data you have is in
sheet1 and the results will be put in sheet 2. I didn't know if you wanted
to perform any sort, but you can easily sort the data using the worksheet
sort. I considered there may not be Start and End times for every event.
The Code looks for the characters "START" and "END" and removes these from
the task name.


'assume data ime in column A and event in column B
Sub splittimes()

With Sheets("Sheet2")
.Range("A1") = "Task"
.Range("B1") = "Start"
.Range("C1") = "End"
.Range("D1") = "Elapse Time"
NewRow = 2
End With

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
TaskTime = .Range("A" & RowCount)
TaskName = .Range("B" & RowCount)
'remove start or end from task name
Task = Trim(Left(TaskName, InStr(TaskName, "-") - 1))
'get start or end
TaskEvent = Trim(Mid(TaskName, InStr(TaskName, "-") + 1))
'search for event
With Sheets("Sheet2")
Set c = .Columns("A").Find(what:=Task, _
LookIn:=xlValues, lookat:=xlWhole, _
searchdirection:=xlPrevious)
If c Is Nothing Then
'add to end of worksheet
.Range("A" & NewRow) = Task
Select Case TaskEvent

Case "START"
.Range("B" & NewRow) = TaskTime
Case "END"
.Range("C" & NewRow) = TaskTime
End Select

NewRow = NewRow + 1
Else

Select Case TaskEvent
Case "START"
'check if last event had a start time
If c.Offset(0, 1) = "" Then
c.Offset(0, 1) = TaskTime
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("B" & NewRow) = TaskTime
NewRow = NewRow + 1
End If
Case "END"
'check if last event had an end time
If c.Offset(0, 2) = "" Then
c.Offset(0, 2) = TaskTime
Else
'add event to end of list
.Range("A" & NewRow) = Task
.Range("C" & NewRow) = TaskTime
NewRow = NewRow + 1
End If
End Select

End If

End With
Next RowCount
End With

With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'add formula to column D
.Range("D2").Formula = "=C2-B2"
'copy formula down worksheet
.Range("D2").Copy _
Destination:=.Range("D2:D" & LastRow)
.Columns("D").NumberFormat = "[H]:mm:ss"
End With

End Sub


"Mohan" wrote:

Hi

I have a system log that captures the start & end time of each process.
Based on the combination of different log description, I have narrowed it
down to the follwing events: CALC-START, CALC-END,
CBVIEW-START,CBVIEW-END,ALLOC-START, ALLOC-END, OLAP-START, OLAP-END

Note: these events will not be in any specific order. Since they run on
different threads, depending on the volum of data, they may complete in any
order.
Also, we may have a CALC-START and another CALC-START without the CALC-END,
because of some issues the job was manually cancells.

The ask:
Is there a way to re-organize the data and out the start time and end time
in different columns so the duration can be caclucated for each event.?

Time Event
10/09/2009 22:28:05 ALLOC-END
10/09/2009 22:03:28 ALLOC-START
10/09/2009 22:02:29 CBVIEW-START
10/09/2009 22:02:07 CALC-END
10/09/2009 19:42:40 CALC-START
10/06/2009 1:23:26 OLAP-END
10/06/2009 0:49:59 CBVIEW-END
10/06/2009 0:44:23 OLAP-START
10/06/2009 0:44:22 ALLOC-END
10/05/2009 23:49:38 ALLOC-START
10/05/2009 23:48:10 CBVIEW-START
10/05/2009 23:48:01 CALC-END
10/05/2009 21:51:23 CALC-START