Comparing times in excel 1

[Asspin]

Ok, so I have been asked to find which value is higher in a column of times in excel. Currently I am using the following...

    If Range("Z24").Value > Range("Z25").Value Then
        strDIUnd = Range("Z24").Text & " @ " & Range("X24").Text & "-" & Range("Y24").Text & " MT"
    Else
        strDIUnd = Range("Z25").Text & " @ " & Range("X25").Text & "-" & Range("Y25").Text & " ET"
    End If
[code]

This is working fine, however, if the time is less than a minute, that value is chosen.  So if I have 3:25 and 4:18, it takes 4:18.  If I have :24 and 4:18 it will pick :24.  Is there any way to get around this?

All help is appreciated!

Dan

 

[PHV]

You may try this:

If TimeValue("0:0" & Range("Z24")) > TimeValue("0:0" & Range("Z25")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Asspin]

Hmm that looks like the right direction, but I am getting a Type Mismatch error with that. I tried with .text after each cell range and that goes through, but I don't believe that will give me the correct results.

Dan

 

[Asspin]

Ok, got it! As always, thanks for the knowledge PHV!

If TimeValue("0" & Range("Z24").Text) > TimeValue("0" & Range("Z25").Text) Then

Dan

 

[Asspin]

Ok, so now I had a problem with times greater than 9:59 due to the 0 being added to the front it was causing the time to end up like 010:00 etc. Here is the solution I came up with, in case it helps anyone else.

    If StringCheck(Range("Z19").Text) > StringCheck(Range("Z25").Text) Then

Private Function StringCheck(sIn)
    If InStr(1, Left(sIn, 2), ":") = 0 Then
        StringCheck = sIn
    Else
        StringCheck = "0" & sIn
    End If
End Function

Dan