Excel (or Access) Challenge

M

Mohan

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
 
J

Joel

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
 
P

Paul

Assuming your time stamp is in column A and the text in column B

In column C
=IF(right("B2",5)="START",A2,"")
in Column D
=IF(right("B2",3)="END",A2,"")

That will get the start and end into separate columns
 
J

Joel

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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Mohan

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

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 said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find then highlight in yellow 6
macro adding rows 8
Form return lookup values 1
Too negative XIRR? 3
Autofil on variable rows, delete extract and show remaining rows 3
Need to pull <=14 Days, <=30 Days, >30 Days from a date column 3
Grouping query to elminate dups 2
OT: Selective HP drivers (apologies for length) 7

Top