Min and Max of array of times

[Ghost308]

I have an array of strings representing times in AM/PM style. The strings in my TimeArray() are in "12:00 AM" formatting. What would be the best way to find the min and max times in the array? I've written a function that calculates the average time successfully, but I don't understand why the min and max functions I wrote don't work.

Public Function FindMinTime(TimeArray() As String) As String
Dim Min As String

'grab length of array
Length = 0
Do
Length = Length + 1
Loop Until TimeArray(Length) = ""

'evaluate total of all minutes
For x = 0 To Length - 1

If Mid(TimeArray(x), 2, 1) = ":" Then
hours = Val(Mid(TimeArray(x), 1, 1)) '+ 12

If Right(TimeArray(x), 2) = "PM" And hours <> 10 _
And hours <> 11 And hours <> 12 Then hours = hours + 12

minutes = Val(Mid(TimeArray(x), 3, 2))
End If

If Mid(TimeArray(x), 3, 1) = ":" Then
hours = Val(Mid(TimeArray(x), 1, 2)) + 12

'If Right(TimeArray(x), 2) = "PM" And hours <> 10 _
'And hours <> 11 And hours <> 12 Then hours = hours + 12

minutes = Val(Mid(TimeArray(x), 4, 2))
End If

thistime = hours * 60 + minutes

If x = 0 Then Min = thistime

If x > 0 And thistime < Min Then Min = thistime

Next x

'convert total of minutes back to regular time
Dim buffer As String

hours = Int(Min / 60)
minutes = Min Mod 60


If minutes < 10 Then minutes = "0" & minutes
If hours > 12 Then hours = hours - 12


buffer = hours & ":" & minutes


'verify am / pm
If Val(Mid(buffer, 1, 1)) < 8 And Val(Mid(buffer, 1, 1)) > 0 And Mid(buffer, 2, 1) = ":" Then
buffer = buffer & " PM"
ElseIf Val(Mid(buffer, 1, 2)) = "12" Then
buffer = buffer & " PM"
Else
buffer = buffer & " AM"
End If

FindMinTime = buffer
End Function


This works by taking the number before the ":" and adding 12 to it if "PM" is labeled. Then it takes the number after the ":". It multiplies the first number by 60 (converting hours to minutes) and then adds the second number (adding the minutes in the current hour). With that total of minutes I should be able to compare to other totals but for some reason "7:00 PM" is computed as a min while "4:15 PM" is computed as a max. I realize this post is a little extensive but I've been playing with this code for two hours now and its driving me crazy! Thank you, anyone who can offer any suggestions!!!

Jeff

[Iouri]

Just a suggestion

1.Convert times in array to numbers
if right(Arr(i),2)= "AM" then
x = Hours*60 + Min
else
x = (Hours +12)*60 + Min
end if

2. Write this to another array
3. Sort new array

The first element is Min, the last is Max

If you need you can convert them back to AM/PM format


If you need sorting procedure, let me know


Iouri Boutchkine
iouri@hotsheet.com

[Ghost308]

Thanks for the swift reply! That is pretty much the psuedocode for the function I've written... the bubble sort i set up works fine in one of my other functions but only sometimes in this one. Its like any time value within the hours of 6:00 and 7:00 clog up the works. If I can't figure out where the slip is in my code I might just an excel sheet to sort and average. Thank you for the help!