PDA

View Full Version : Solved: Tracking Steps through an Order



babsc01
08-17-2004, 07:16 AM
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
08-17-2004, 05:00 PM
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
08-18-2004, 06:09 AM
Thanks, Jacob! I will take a look and let you know what happens...

Jacob Hilderbrand
08-18-2004, 06:11 AM
You're Welcome

If there are any problems let me know.

babsc01
08-18-2004, 06:52 AM
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
08-18-2004, 03:26 PM
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
08-18-2004, 06:18 PM
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
08-18-2004, 06:36 PM
Keep coming here and you will learn how to code in no time. :)

Zack Barresse
08-18-2004, 07:54 PM
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
08-19-2004, 05:00 AM
DRJ...one last thing. I'm gonna try it myself, but in case it fails...any easy way to add MINUTES? Thanks!

Jacob Hilderbrand
08-19-2004, 06:07 AM
You can calculate the minutes by multiplying the remaining fractional hours by 60. Multiply by 60 again to get the seconds.


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

babsc01
08-19-2004, 06:12 AM
As always, thanks so much for the help.

Jacob Hilderbrand
08-19-2004, 06:23 AM
Glad to help.

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

babsc01
08-19-2004, 06:37 AM
Nope...to the minute will be more than adequate. Thanks!

babsc01
08-19-2004, 08:46 AM
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.

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

Jacob Hilderbrand
08-19-2004, 03:48 PM
This should get rid of any 0 times:


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

babsc01
08-20-2004, 04:33 AM
Thanks once again!!!

Jacob Hilderbrand
08-20-2004, 06:34 AM
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.