将字符串转换为日期VBA [英] Convert String to Date VBA

问题描述

我有一个日期和时间的字符串，格式为DD.MM.YYYY HH：mm，我想转换为日期（无论格式如何）。

我使用这个测试代码，我找不到为什么不工作：

  Dim Data1 As String，Data2 As Date 
 Data1 =01.01.2015 01:01
 Data2 = CDate（Data1）
  

解决方案

尝试将字符串更接近传统的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作为变量
 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 
  / pre> 
 
 
 VBA使用第一种方法做出最好的猜测，并在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.

                        
这篇关于将字符串转换为日期VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！