VBA如何在变量中存储时间戳 [英] VBA how to store timestamp in variable

问题描述

我正在处理日志或历史文件。在第1列中有这样的时间戳记：WEST Mar 01 22:08:01 EST2017。
我正在将工作表的全部内容复制到2d变量数组中以进行进一步处理。现在，我想将此时间戳记存储为日期或日期时间，以进一步处理它。

I am working on processing of log or history file. In column 1 there is timestamp like this Wed Mar 01 22:08:01 EST 2017. I am copying the entire contents of the sheet into 2d variant array for further processing. Now I want to store this timestamp as date or datetime to further process it.

但是如果我这样做

将dTimeStamp设置为日期

dim dTimeStamp as date

，然后在循环中使用它存储column1
的值dTimeStamp = cdate（vbaseArray（I，1））

and in the loop use it to store the value of column1 dTimeStamp = cdate(vbaseArray(I,1))

它会给我带来typemismatch错误。

it gives me typemismatch error.

有没有办法将时间戳记值存储为日期或日期时间？
请提出建议。

Is there a way I can store timestamp value as date or datetime? Please suggest.

推荐答案

您无法将该字符串自动转换为日期，因此必须对其进行解析。通常， DateValue 比 CDate 宽容得多。

You can't convert that string into a date automatically, so you have to parse it. Generally, DateValue is a little more forgiving than CDate.

从即时窗口

?datevalue(mid("Wed Mar 01 22:08:01 EST 2017",5,15))
3/1/2017 

这将始终为您提供当前年份。

That will always give you the current year. It cuts of the day of week and stops after the time.

如果需要年份，或者只是想变得更强大，我可以使用类似

If you need the year, or you just want to be more robust, I'd use a function like

Function ConvertDate(ByVal sDate As String) As Date

    Dim vaSplit As Variant

    vaSplit = Split(sDate, Space(1))

    ConvertDate = DateValue(vaSplit(1) & Space(1) & vaSplit(2) & ", " & vaSplit(5)) + TimeValue(vaSplit(3))

End Function

在即时窗口中

?convertdate("Wed Mar 01 22:08:01 EST 2017")
3/1/2017 10:08:01 PM 

这篇关于VBA如何在变量中存储时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！