Tracking Steps through an Order

[babsc01]

Hello all...would like some input on this.

See attachment...for each ORDER NUMBER, I'd like to, somehow, display the elapsed time (using STATION DATE) between each step shown, and another display showing elapsed time between ENTERED and BILLED.

I'm thinking a User Form, with a list box to select the ORDER NUMBER. The form would also contain fields showing TOTAL ELAPSED TIME (between ENTERED and BILLED) and AVERAGE TIME between each step.

Would also like to display OVERALL AVERAGE TIME for all ORDER NUMBERS in the range. My attachment is just a smidgen. The actual range I need this for is approx. 47000 lines.

Any ideas or help with this beast would be appreciated. :cool

[Jacob Hilderbrand]

This macro will give you the elapsed time in days.

Option Explicit
 
Sub ElapsedTime()
Dim x               As Long
Dim LastRow         As Long
Dim StartTime       As Double
Dim EndTime         As Double
LastRow = Range("A65536").End(xlUp).Row
    For x = 2 To LastRow
        Select Case Range("C" & x).Text
            Case Is = "ENTERED"
                StartTime = Range("D" & x).Value
            Case Is = "BILLED"
                EndTime = Range("D" & x).Value
                Range("E" & x).Value = EndTime - StartTime
            Case Else
                Range("E" & x).Value = Range("D" & x).Value - Range("D" & x - 1).Value
        End Select
    Next x
End Sub

[babsc01]

Thanks, Jacob! I will take a look and let you know what happens...

[Jacob Hilderbrand]

You're Welcome

If there are any problems let me know.

[babsc01]

DRJ...this macro works GREAT!!! Any way to post the result as "X Days, Y Hours" instead of HH:MM?

I've checked Chip's site (he's the date and time master), but not sure I can fit his lengthy code into the macro.

Thanks again!

[Jacob Hilderbrand]

Try this:

Option Explicit
 
Sub ElapsedTime()
Dim x               As Long
Dim LastRow         As Long
Dim StartTime       As Double
Dim EndTime         As Double
Dim Days            As Long
Dim Hours           As Long
Dim ElapsedTime     As String
LastRow = Range("A65536").End(xlUp).Row
    For x = 2 To LastRow
        Select Case Range("C" & x).Text
            Case Is = "ENTERED"
                StartTime = Range("D" & x).Value
            Case Is = "BILLED"
                EndTime = Range("D" & x).Value
                Range("E" & x).Value = EndTime - StartTime
            Case Else
                Range("E" & x).Value = Range("D" & x).Value - Range("D" & x - 1).Value
        End Select
        Select Case Range("E" & x).Value
            Case Is = vbNullString
            Case Is >= 1
                Days = Int(Range("E" & x).Value)
                Hours = (Range("E" & x) - Days) * 24
                ElapsedTime = ""
                If Days = 1 Then
                    ElapsedTime = Days & " Day and "
                    Else
                    ElapsedTime = Days & " Days and "
                End If
                If Hours = 1 Then
                    ElapsedTime = ElapsedTime & Hours & " Hour"
                    Else
                    ElapsedTime = ElapsedTime & Hours & " Hours"
                End If
                Range("E" & x) = ElapsedTime
            Case Is < 1
                Hours = (Range("E" & x)) * 24
                If Hours = 1 Then
                    ElapsedTime = Hours & " Hour"
                    Else
                    ElapsedTime = Hours & " Hours"
                End If
                Range("E" & x) = ElapsedTime
        End Select
    Next x
End Sub

[babsc01]

DRJ...most appreciative. Exactly what I was looking for. THANKS!!! Hopefully I'll soon be to the level where I can figure this stuff out myself.

[Jacob Hilderbrand]

Keep coming here and you will learn how to code in no time.

[Zack Barresse]

Or just study Jake's code and his explanation of it. I've found that's one of the best ways to learn. Thanks Jake!

[babsc01]

DRJ...one last thing. I'm gonna try it myself, but in case it fails...any easy way to add MINUTES? Thanks!

[Jacob Hilderbrand]

You can calculate the minutes by multiplying the remaining fractional hours by 60. Multiply by 60 again to get the seconds.

[VBA]
Option Explicit

Sub ElapsedTime()

Dim x As Long
Dim LastRow As Long
Dim StartTime As Double
Dim EndTime As Double
Dim Days As Long
Dim Hours As Long
Dim Minutes As Long
Dim ElapsedTime As String

LastRow = Range("A65536").End(xlUp).Row
For x = 2 To LastRow
Select Case Range("C" & x).Text
Case Is = "ENTERED"
StartTime = Range("D" & x).Value
Case Is = "BILLED"
EndTime = Range("D" & x).Value
Range("E" & x).Value = EndTime - StartTime
Case Else
Range("E" & x).Value = Range("D" & x).Value - Range("D" & x - 1).Value
End Select
Select Case Range("E" & x).Value
Case Is = vbNullString
Case Is >= 1
Days = Int(Range("E" & x).Value)
Hours = Int((Range("E" & x) - Days) * 24)
Minutes = ((Range("E" & x).Value - Days) * 24 - Hours) * 60
ElapsedTime = ""
If Days = 1 Then
ElapsedTime = Days & " Day "
Else
ElapsedTime = Days & " Days "
End If
If Hours = 1 Then
ElapsedTime = ElapsedTime & Hours & " Hour and "
Else
ElapsedTime = ElapsedTime & Hours & " Hours and "
End If
If Minutes = 1 Then
ElapsedTime = ElapsedTime & Minutes & " Minute"
Else
ElapsedTime = ElapsedTime & Minutes & " Minutes"
End If
Range("E" & x) = ElapsedTime
Case Is < 1
Hours = Int((Range("E" & x)) * 24)
Minutes = ((Range("E" & x).Value * 24) - Hours) * 60
If Hours = 1 Then
ElapsedTime = Hours & " Hour and "
Else
ElapsedTime = Hours & " Hours and "
End If
If Minutes = 1 Then
ElapsedTime = ElapsedTime & Minutes & " Minute"
Else
ElapsedTime = ElapsedTime & Minutes & " Minutes"
End If
Range("E" & x) = ElapsedTime
End Select
Next x

End Sub
[/VBA]

[babsc01]

As always, thanks so much for the help.

[Jacob Hilderbrand]

Glad to help.

So, do you think you will need seconds added or is tracking to the minute enough?

[babsc01]

Nope...to the minute will be more than adequate. Thanks!

[babsc01]

DRJ...I made one little modification. Replaced "and" with commas to show:

X Days, Y Hours, Z Minutes

Now, I'm trying to eliminate the result showing "0 Hours," but having little luck. Any ideas? Thanks.

[VBA]Option Explicit

Sub ElapsedTime3()

Dim x As Long
Dim LastRow As Long
Dim StartTime As Double
Dim EndTime As Double
Dim Days As Long
Dim Hours As Long
Dim Minutes As Long
Dim ElapsedTime As String

LastRow = Range("A65536").End(xlUp).Row
For x = 2 To LastRow
Select Case Range("C" & x).Text
Case Is = "ENTERED"
StartTime = Range("D" & x).Value
Case Is = "BILLED"
EndTime = Range("D" & x).Value
Range("E" & x).Value = EndTime - StartTime
Case Else
Range("E" & x).Value = Range("D" & x).Value - Range("D" & x - 1).Value
End Select
Select Case Range("E" & x).Value
Case Is = vbNullString
Case Is >= 1
Days = Int(Range("E" & x).Value)
Hours = Int((Range("E" & x) - Days) * 24)
Minutes = ((Range("E" & x).Value - Days) * 24 - Hours) * 60
ElapsedTime = ""
If Days = 1 Then
ElapsedTime = Days & " Day, "
Else
ElapsedTime = Days & " Days, "
End If
If Hours = 1 Then
ElapsedTime = ElapsedTime & Hours & " Hour, "
Else
ElapsedTime = ElapsedTime & Hours & " Hours, "
End If
If Minutes = 1 Then
ElapsedTime = ElapsedTime & Minutes & " Minute"
Else
ElapsedTime = ElapsedTime & Minutes & " Minutes"
End If
Range("E" & x) = ElapsedTime
Case Is < 1
Hours = Int((Range("E" & x)) * 24)
Minutes = ((Range("E" & x).Value * 24) - Hours) * 60
If Hours = 1 Then
ElapsedTime = Hours & " Hour, "
Else
ElapsedTime = Hours & " Hours, "
End If
If Minutes = 1 Then
ElapsedTime = ElapsedTime & Minutes & " Minute"
Else
ElapsedTime = ElapsedTime & Minutes & " Minutes"
End If
Range("E" & x) = ElapsedTime
End Select
Next x

End Sub [/VBA]

[Jacob Hilderbrand]

This should get rid of any 0 times:

[vba]
Option Explicit

Sub ElapsedTime3()

Dim x As Long
Dim LastRow As Long
Dim StartTime As Double
Dim EndTime As Double
Dim Days As Long
Dim Hours As Long
Dim Minutes As Long
Dim ElapsedTime As String

LastRow = Range("A65536").End(xlUp).Row
For x = 2 To LastRow
Select Case Range("C" & x).Text
Case Is = "ENTERED"
StartTime = Range("D" & x).Value
Case Is = "BILLED"
EndTime = Range("D" & x).Value
Range("E" & x).Value = EndTime - StartTime
Case Else
Range("E" & x).Value = Range("D" & x).Value - Range("D" & x - 1).Value
End Select
Select Case Range("E" & x).Value
Case Is = vbNullString
Case Is >= 1
Days = Int(Range("E" & x).Value)
Hours = Int((Range("E" & x) - Days) * 24)
Minutes = ((Range("E" & x).Value - Days) * 24 - Hours) * 60
If Days = 1 Then
ElapsedTime = Days & " Day, "
Else
ElapsedTime = Days & " Days, "
End If
If Hours = 1 Then
ElapsedTime = ElapsedTime & Hours & " Hour, "
ElseIf Hours = 0 Then
Else
ElapsedTime = ElapsedTime & Hours & " Hours, "
End If
If Minutes = 1 Then
ElapsedTime = ElapsedTime & Minutes & " Minute"
ElseIf Minutes = 0 Then
Else
ElapsedTime = ElapsedTime & Minutes & " Minutes"
End If
Range("E" & x) = ElapsedTime
Case Is < 1
Hours = Int((Range("E" & x)) * 24)
Minutes = ((Range("E" & x).Value * 24) - Hours) * 60
If Hours = 1 Then
ElapsedTime = Hours & " Hour, "
ElseIf Hours = 0 Then
Else
ElapsedTime = Hours & " Hours, "
End If
If Minutes = 1 Then
ElapsedTime = ElapsedTime & Minutes & " Minute"
ElseIf Minutes = 0 Then
Else
ElapsedTime = ElapsedTime & Minutes & " Minutes"
End If
Range("E" & x) = ElapsedTime
End Select
ElapsedTime = ""
Next x

End Sub
[/vba]

[babsc01]

Thanks once again!!!

[Jacob Hilderbrand]

Glad to help.

If you need anymore help, or want a clarification on what some of the lines of code are doing let me know.