Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - Convert String to Date VBA

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)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...