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 ...
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...
zz is probably the one you're interested in:
but make sure that months and days aren't transposed in your neck of the woods.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
Perfect! I knew it was simple but couldn't find the command names.