Remember Me?

 Posts: n/a 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.

 Chip Pearson Posts: n/a 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.

 JE McGimpsey Posts: n/a 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

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 Posts: n/a 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

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
 JE McGimpsey Posts: n/a 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post matteo Excel Discussion (Misc queries) 0 June 16th 05 07:43 PM jbsand1001 Excel Worksheet Functions 1 June 9th 05 06:15 PM Sue Excel Worksheet Functions 1 March 30th 05 04:35 PM boris Excel Worksheet Functions 2 March 16th 05 02:13 PM MarianneR Excel Worksheet Functions 4 November 18th 04 03:24 PM

All times are GMT +1. The time now is 03:08 PM. Copyright ©2004-2021 ExcelBanter.