Results 1 to 4 of 4

VBA convert time string to time value

This is a discussion on VBA convert time string to time value within the Excel Questions forums, part of the Question Forums category; I have an array where I read in the contents of a file. One column of that array is a ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    864

    Default VBA convert time string to time value

    I have an array where I read in the contents of a file. One column of that array is a date ("yyyy/mm/dd") and another is a time ("hh:mm:ss"). I need to take the first and last time and calculate the difference by subtracting. I'm getting an error, I suspect because they are stored as strings and not numbers. I'm also concerned that if the two events happen across midnight the answer will be wrong, so I think I need to add the date value to the time value before subtracting. This would also require them to to be values.

    Q: How do I convert these strings to proper date and time values? Or, how do I read them into my array in as serial numbers to begin with? The array was defined as "Variant" if that makers a difference...
    Roscoe

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,017

    Default Re: VBA convert time string to time value

    Use the DateValue function and TimeValue function. They convert a text string into a serial date and time value. VBA Help has some good examples for these two functions.

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: VBA convert time string to time value

    zz is probably the one you're interested in:
    Code:
    Sub blah()
    x = "2010/03/02"
    y = "13:43:21"
    xx = DateValue(x)
    yy = TimeValue(y)
    zz = DateValue(x) + TimeValue(y)
    'MsgBox Format(xx, "ddd dd MMM yyyy hh:mm:ss") & vbLf & Format(yy, "hh:mm:ss") & vbLf & Format(zz, "ddd dd MMM yyyy hh:mm:ss")
    End Sub
    but make sure that months and days aren't transposed in your neck of the woods.

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Posts
    864

    Default Re: VBA convert time string to time value

    Perfect! I knew it was simple but couldn't find the command names.
    Roscoe

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com