迄今为止的VBA字符串(以毫秒为单位) [英] VBA string with milliseconds to date
问题描述
我有一个字符串,其格式为 yyyy-mm-dd hh:mm:ss.mmm
(末尾为毫秒)
I have a string in the form "yyyy-mm-dd hh:mm:ss.mmm"
(where the end is milliseconds)
我想将其转换为数字,最好是 Date
,该数字可以保留所有信息
I'd like to convert it to a number, preferably a Date
, which preserves all the information
我尝试过 CDate()
,例如。
Dim dateValue As Date
dateValue = CDate("2017-12-23 10:29:15.223")
但是出现类型不匹配错误
But get a type mismatch error
推荐答案
A 日期
类型保存自1899年12月30日以来的天数,精度为一秒。尽管仍然可以通过将日期存储为货币类型来保留毫秒,因为与日期/双精度相比,它可以容纳4个额外的数字。
A Date
type holds the number of days since December 30 1899
with a precision of one second. Though it's still possible to hold the milliseconds by storing the date in a currency type since it can hold 4 extra digits compared to a Date/Double.
因此,另一种选择是将日期作为时间戳存储在 Currency
类型中,表示自 1899年12月30日
以来的秒数:
So an alternative would be to store the date as a timestamp in a Currency
type representing the number of seconds since December 30 1899
:
Public Function CDateEx(text As String) As Currency
Dim parts() As String
parts = Split(text, ".")
CDateEx = CCur(CDate(parts(0)) * 86400) + CCur(parts(1) / 1000)
End Function
并将时间戳转换回字符串:
And to convert the timestamp back to a string:
Public Function FormatDateEx(dt As Currency) As String
FormatDateEx = Format(dt / 86400, "yyyy-mm-dd HH:mm:ss") & "." & ((dt - Fix(dt)) * 1000)
End Function
这篇关于迄今为止的VBA字符串(以毫秒为单位)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!