我正在尝试以下非常简单的VBA代码转换为Datetime
Sub datetimedifffference()
Dim d As String
Dim sd As Date
d = "2021-04-06T12:56:16+0000"
sd = Format(CDate(d), "mm-dd-yyyy hh:mm:ss")
Debug.Print sd
End Sub
但它给出了类型不匹配错误如有任何帮助,将不胜感激
我在下面再次尝试,但时间差正在减少,而不是增加
Sub datetimedifffference()
Dim d As String
Dim sd As Long
d = "2021-04-06T12:56:16+0000"
sd = DateDiff("n", Now, Format(convertStringtoDate(d))
Debug.Print sd
End Sub
Function convertStringtoDate(stringdate As String) As String
Dim strings() As String
strings = Split(stringdate, "T")
convertStringtoDate = strings(0) & " " & Left(strings(1), 8)
'Debug.Print convertStringtoDate
End Function
T
和+0000
应该被替换。另外请注意,日期是一个数字,格式化日期是字符串。
Sub datetimedifffference()
Dim d As String
Dim sd As Date
d = "2021-04-06T12:56:16+0000"
d = Replace(d, "T", " ")
d = Replace(d, "+0000", "")
sd = CDate(d)
Debug.Print sd
End Sub
您可以使用以下通用函数:
' Converts an ISO 8601 formatted date/time string to a date value.
'
' A timezone info is ignored.
' Optionally, a millisecond part can be ignored.
'
' Examples:
' 2029-02-17T19:43:08 +01.00 -> 2029-02-17 19:43:08
' 2029-02-17T19:43:08 -> 2029-02-17 19:43:08
' ' IgnoreMilliseconds = False
' 2029-02-17T19:43:08.566 -> 2029-02-17 19:43:08.566
' ' IgnoreMilliseconds = True
' 2029-02-17T19:43:08.566 -> 2029-02-17 19:43:08.000
'
' 2017-05-24. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CDateIso8601( _
ByVal Expression As String, _
Optional ByVal IgnoreMilliseconds As Boolean) _
As Date
Const Iso8601Separator As String = "T"
Const NeutralSeparator As String = " "
' Length of ISO 8601 date/time string like: 2029-02-17T19:43:08 [+00.00]
Const Iso8601Length As Integer = 19
' Length of ISO 8601 date/time string like: 2029-02-17T19:43:08.566
Const Iso8601MsecLength As Integer = 23
Dim Value As String
Dim Result As Date
Value = Replace(Expression, Iso8601Separator, NeutralSeparator)
If InStr(Expression, MillisecondSeparator) <> Iso8601Length + 1 Then
IgnoreMilliseconds = True
End If
If IgnoreMilliseconds = False Then
Result = CDateMsec(Left(Value, Iso8601MsecLength))
Else
Result = CDate(Left(Value, Iso8601Length))
End If
CDateIso8601 = Result
End Function
然后将您的格式应用于返回的值。
查看您的编辑,我认为您需要更改两个日期的顺序:
Sub datetimedifffference()
Dim d As String
Dim sd As Long
d = "2021-04-06T12:56:16+0000"
sd = DateDiff("n", convertStringtoDate(d), Now)
Debug.Print sd
End Sub
Function convertStringtoDate(stringdate As String) As String
Dim strings() As String
strings = Split(stringdate, "T")
convertStringtoDate = CDate(strings(0) & " " & Left(strings(1), 8))
End Function