+ Reply to Thread
Results 1 to 5 of 5

compare time in IF statement

  1. #1
    Guest

    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. #2
    Chip Pearson
    Guest

    Re: compare time in IF statement

    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 news:[email protected]...
    > 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. #3
    JE McGimpsey
    Guest

    Re: compare time in IF statement

    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 <[email protected]>, <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. #4
    Dave Peterson
    Guest

    Re: compare time in IF statement

    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 <[email protected]>, <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. #5
    JE McGimpsey
    Guest

    Re: compare time in IF statement

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

    In article <[email protected]>,
    Dave Peterson <[email protected]> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1