Comparing Times in Text Boxes to Times in Cells

I need to use the following arguements in an If statement, but its
tripping on itself when it shouldn't be

Format(Worksheets("Data").Cells(R, 4).Value, "h:mm AM/PM") > _
Format(Me.ETOA.Value, "h:mm AM/PM") Or _
Format(Worksheets("Data").Cells(R, 4).Value, "h:mm AM/PM") = _
Format(Me.ETOA.Value, "h:mm AM/PM")) And _
Format(Worksheets("Data").Cells(R - 1, 4).Value, "h:mm AM/PM") < _
Format(Me.ETOA.Value, "h:mm AM/PM")

The third arguement is evaluating to false when it shouldn't:
specifically, when the text box has 11:00 AM in it and Cell(R, 4).Value
is 11:00 AM and Cell(R - 1, 4).Value is 9:50 AM

is there a better way to present this arguement to an if statement?
(i'm sure there must be, but just comparing values was no good because
Me.ETOA.Value is not being recognized as a time without having the
Format)

Thanks again guys

Matt -

You are comparing the sort order of the text representation of the times, so
naturally 9:50 is greater than 11:00, since "9" is greater than "1". You
need to compare numerical times. Presumably the cells contain numerical
times, but the textboxes contain text, by definition. Convert the textbox to
a time using

CDate(Me.ETOA.Value)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Matt" <[email protected]> wrote in message
news:[email protected]...
>I need to use the following arguements in an If statement, but its
> tripping on itself when it shouldn't be
>
> Format(Worksheets("Data").Cells(R, 4).Value, "h:mm AM/PM") > _
> Format(Me.ETOA.Value, "h:mm AM/PM") Or _
> Format(Worksheets("Data").Cells(R, 4).Value, "h:mm AM/PM") = _
> Format(Me.ETOA.Value, "h:mm AM/PM")) And _
> Format(Worksheets("Data").Cells(R - 1, 4).Value, "h:mm AM/PM") < _
> Format(Me.ETOA.Value, "h:mm AM/PM")
>
> The third arguement is evaluating to false when it shouldn't:
> specifically, when the text box has 11:00 AM in it and Cell(R, 4).Value
> is 11:00 AM and Cell(R - 1, 4).Value is 9:50 AM
>
> is there a better way to present this arguement to an if statement?
> (i'm sure there must be, but just comparing values was no good because
> Me.ETOA.Value is not being recognized as a time without having the
> Format)
>
> Thanks again guys
>