从文本格式到日期格式的日期转换部分工作

Question

我想将日期从文本格式转换为日期格式。我在下面提供的代码适用于所有具有dd大于12的数字的日期。因此，对于dd = 13到dd = 31，它可以正常工作。当我与dd < 13约会时，那么它就不起作用。我在下面提供的日期是代码不能工作的日期的一个例子。它将单元格B1清空。此外，我的系统设置设置为dd/mm/yyyy，因此可以与系统配合使用。任何人都知道为什么它不起作用？

Sheets("1").Select 
Dim myValue As Variant 
myValue = InputBox("Simi, type the date of the shipments that you want to create the report for", "Date", 1) 
Sheets("1").Range("B1").Value = myValue 
With Sheets("1").Range("B1") 
Dim t() As String 
t = Split(myValue, "/") ' Split into pieces 
If UBound(t) = 2 Then ' If 3 elements found: 
myValue = DateSerial(t(2), t(1), t(0)) ' Convert to Date 
End If 
End With 

Answer 1

说实话，这有点难以理解你的代码 - 你在某种程度上处理VBA中的Excel公式 - 不需要这样做。

尝试像

Dim r As Range 
    Set r = ThisWorkbook.Sheets(1).Range("B1") 
    r.Value = "08/01/2017" 
    With r 
     Dim s As String, t() As String 

     s = r.Value ' Read cell content into string 
     t = Split(s, "/") ' Split into pieces 
     If UBound(t) = 2 Then ' If 3 elements found: 
      r.Value = DateSerial(t(2), t(1), t(0)) ' Convert to Date 
     End If 
    End With 

用户输入（无需转换之前将其写入单元格）版本

Dim myValue As Variant 
    myValue = InputBox("Simi, type the date of the shipments that you want to create the report for", "Date", 1) 
    Dim r As Range 
    Set r = ThisWorkbook.Sheets(1).Range("B1") 
    With r 
     Dim s As String, t() As String 

     s = myValue ' Use user input 
     t = Split(s, "/") ' Split into pieces 
     If UBound(t) = 2 Then ' If 3 elements found: 
      r.Value = DateSerial(t(2), t(1), t(0)) ' Convert to Date 
     End If 
    End With