更新时间:2023-01-30 18:38:26
尝试将字符串更接近传统的EN-US格式,然后转换为 CDate
。
Dim Data1 As String,Data2 As Date
Data1 = Replace(01.01.2015 01:01 ,Chr(46),Chr(47))
Data2 = CDate(Data1)
Debug.Print Data2
fwiw,提供从MDY和DMY格式产生不明确结果的样本数据通常不是一个好主意。这可能是VBA中的一个问题。供应数据是明确的一个或另一个。
对于包含模糊的DMY / MDY数据的字符串,如02.01.2015 01:01 code>,这是一个更好的方法。
Dim Data1 As String,Data2 As Date,vDATE As Variant,vTIME作为变量
/ pre>
Data1 =02.01.2015 01:01
vTIME = Split(Data1,Chr(32))
vDATE = Split(vTIME(0),Chr(46))
Data2 = DateSerial(vDATE(2),vDATE(1),vDATE(0))+ TimeValue(vTIME(1))
Debug.Print Data2VBA使用第一种方法做出***的猜测,并在2015年2月1日发布错误。第二种方法是更确定的,并获得了2015年1月02日的正确答案(假设已知的DMY日期格式)。总之,得到日期并不总是足够的;确保它是正确的日期。
I have a String with a date and a time in this format "DD.MM.YYYY HH:mm" which I want to convert to a date (regardless of the format).
I am using this test code, and I can't find why doesn't it work:
Dim Data1 As String, Data2 As Date Data1 = "01.01.2015 01:01" Data2 = CDate(Data1)
Try getting the string into something closer to a conventional EN-US format before converting with
CDate
.Dim Data1 As String, Data2 As Date Data1 = Replace("01.01.2015 01:01", Chr(46), Chr(47)) Data2 = CDate(Data1) Debug.Print Data2
fwiw, it usually isn't a good idea to provide sample data that produces ambiguous results from MDY and DMY formats. This could be an issue in VBA. Supply data that is definitively one or the other.
For strings containing ambiguous DMY/MDY data like
"02.01.2015 01:01"
, this is a better approach.Dim Data1 As String, Data2 As Date, vDATE As Variant, vTIME As Variant Data1 = "02.01.2015 01:01" vTIME = Split(Data1, Chr(32)) vDATE = Split(vTIME(0), Chr(46)) Data2 = DateSerial(vDATE(2), vDATE(1), vDATE(0)) + TimeValue(vTIME(1)) Debug.Print Data2
VBA makes a 'best guess' using the first method and comes out wrong as 01-Feb-2015. The second method is more definitive and achieves the correct answer of 02-Jan-2015 (assuming a known DMY date format). In short, getting a date isn't always enough; make sure it is the correct date.