VBA convert time string to time value

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,041
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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...
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
Perfect! I knew it was simple but couldn't find the command names.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top