Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default Comparing Times in Text Boxes to Times in Cells

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" wrote in message
ps.com...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing the update times of two cells [email protected] Excel Discussion (Misc queries) 7 September 26th 08 11:37 AM
count text appear how many times & put in respec col??eg 1st times Piglet Excel Discussion (Misc queries) 3 May 29th 08 07:53 AM
Comparing times.. [email protected] Excel Worksheet Functions 1 November 25th 07 04:16 AM
=Text(b2-A2,"h") hours between 2 times, what do I do for 10 times Jayda New Users to Excel 3 May 18th 05 05:53 PM
Comparing two times (HH:MM:SS) CopperNic Excel Programming 1 January 8th 04 09:24 AM


All times are GMT +1. The time now is 03:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"