可以利用转换格式的函数来解决。如图所示:
这种情况最完美的代码:
Sub test3()
Dim text As Date
Dim totalRow As Integer '如果数据行数不确定,可以动态定义一下,动态获取
totalRow = Worksheets("sheet1").UsedRange.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For i = 1 To totalRow
text = Sheets("sheet1").Cells(i, 1)
Sheets("sheet1").Cells(i, 5) = Format$(text, "yyyy/m/d")
Sheets("sheet1").Cells(i, 6) = Format$(text, "h:nn:ss")
Next i
End Sub
刚开始做的时候,用截取字符串的方式,有时候会截取的位数不对代码如下:
定义的dateText2是date类型,截取位数不对的话还会报类型错误。
Sub test()
Dim text As Date
Dim c As Integer
Dim dateText1 As Date
Dim dateText2 As Date
text = Sheets("sheet1").Cells(2, 1)
c = InStr(text, " ")
dateText1 = Left(text, InStr(text, " ") - 1) '截取左边日期
dateText2 = Right(text, InStr(text, " ") - 1) '截取右边时刻
End Sub