Re: Pivot, Summarize values by Maximum, 24-hour clock
Thanks, not really sure why its missing some of them, your 93 and 95 change worked well, it just went to next day, maybe the post 95 has to be reflected in 99.
think this was able to do it:
Dim rowCount As Long
Dim lastRow As Long
Dim ws As Worksheet
'Where are our variables?
Const colDate As String = "A"
Const colName As String = "B"
Const colStage As String = "C"
Const colStart As String = "D"
Const colEnd As String = "E"
'Which worksheet is data on?
Set ws = ActiveSheet
Application.ScreenUpdating = False
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For rowCount = lastRow To 2 Step -1
'Check if the same person
If .Cells(rowCount, colName).Value = .Cells(rowCount, colName).Offset(-1).Value And _
.Cells(rowCount, colDate).Value = .Cells(rowCount, colDate).Offset(-1).Value Then
'If start time of next segment was later than end of previous, there's Down Time
If .Cells(rowCount, colStart).Value > .Cells(rowCount, colEnd).Offset(-1).Value Then
'Create new row
'Fill in missing info
'----CHANGED THIS NEXT LINE---
.Cells(rowCount, colStage).Value = "Down Time"
.Cells(rowCount, colStart).Value = .Cells(rowCount, colEnd).Offset(-1).Value
.Cells(rowCount, colEnd).Value = .Cells(rowCount, colStart).Offset(1).Value
Application.ScreenUpdating = True
Say if a downtime happened twice a day, can we name these 1 and 2 , and number them per day?