Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default compare time in IF statement

hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes 0.39583333,
the time serial code.
but its still useless when I write if range(M2).value = 0.39583333 then DO
SOMETHING




thnks.

  #2   Report Post  
Chip Pearson
 
Posts: n/a
Default

Try something like

If Range("M2").Value = TimeSerial(9,30,0) Then
' do something
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<a wrote in message ...
hi,

case 1
i have this column, with time in US locale and in the format
H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes
0.39583333,
the time serial code.
but its still useless when I write if range(M2).value =
0.39583333 then DO
SOMETHING




thnks.



  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

One way:

Case 1:

If Range("M2").Value = #9:30:00# Then

Note that Time(9,30,0) gives a type mismatch error. You could use
TimeSerial(9, 30, 0) instead:

If Range("M2").Value = TimeSerial(9, 30, 0)Then

Case 2:

XL stores values to 15 digits of precision, so 9:30:00 =
0.395833333333333, which is why your comparison didn't work. Even then,
small rounding errors make exact comparisons difficult. You might think
of using something like:

If (CDbl(Range("M2").Value) - 0.395833) < 1e-6 Then

(1 second is a bit more than 1e-5.


In article , <a wrote:

hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes 0.39583333,
the time serial code.
but its still useless when I write if range(M2).value = 0.39583333 then DO
SOMETHING




thnks.

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

I think I'd use:

If abs(CDbl(Range("M2").Value) - 0.395833)) < 1e-6 Then

to cover both sides of 9:30

JE McGimpsey wrote:

One way:

Case 1:

If Range("M2").Value = #9:30:00# Then

Note that Time(9,30,0) gives a type mismatch error. You could use
TimeSerial(9, 30, 0) instead:

If Range("M2").Value = TimeSerial(9, 30, 0)Then

Case 2:

XL stores values to 15 digits of precision, so 9:30:00 =
0.395833333333333, which is why your comparison didn't work. Even then,
small rounding errors make exact comparisons difficult. You might think
of using something like:

If (CDbl(Range("M2").Value) - 0.395833) < 1e-6 Then

(1 second is a bit more than 1e-5.

In article , <a wrote:

hi,

case 1
i have this column, with time in US locale and in the format H:MM , for
example, 9:30 in cell M2

My VBA code tries to compare

if range(M2).value = time(9,30,0) then DO SOMETHING

just fails to work..
---------------------

case 2,
when i tried to convert the whole column to text, 9:30 becomes 0.39583333,
the time serial code.
but its still useless when I write if range(M2).value = 0.39583333 then DO
SOMETHING




thnks.


--

Dave Peterson
  #5   Report Post  
JE McGimpsey
 
Posts: n/a
Default

yup - did it on one post, forgot on the other. Thanks.

In article ,
Dave Peterson wrote:

I think I'd use:

If abs(CDbl(Range("M2").Value) - 0.395833)) < 1e-6 Then

to cover both sides of 9:30

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
Lookup and compare to (today) + time and get result matteo Excel Discussion (Misc queries) 0 June 16th 05 07:43 PM
Compare 2 cells values with one IF statement jbsand1001 Excel Worksheet Functions 1 June 9th 05 06:15 PM
If Statement - in time not decimal minutes Sue Excel Worksheet Functions 1 March 30th 05 04:35 PM
unmet challenge boris Excel Worksheet Functions 2 March 16th 05 03:13 PM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 4 November 18th 04 04:24 PM


All times are GMT +1. The time now is 09:27 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"