|
可在VBA的模块中复制下列自定义函数
Function changedate(source As Range)
Dim result As String
If IsDate(source) Then
result = WorksheetFunction.Text(Year(source), "0000") & "." & WorksheetFunction.Text(Month(source), "00") & _
"." & WorksheetFunction.Text(Day(source), "00")
Else
Dim n1, n2 As Integer
Dim str1, str2, str3 As String
n1 = WorksheetFunction.Find(".", source)
n2 = WorksheetFunction.Find(".", source, n1 + 1)
str1 = Left(source, n1 - 1)
str2 = Mid(source, n1 + 1, n2 - n1)
str3 = Right(source, Len(source) - n2)
result = WorksheetFunction.Text(str1, "0000") & "." & WorksheetFunction.Text(str2, "00") & "." & _
WorksheetFunction.Text(str3, "00")
End If
changedate = result
End Function
假定B列为包括各种格式日期的源单元格,C列为转换日期后的目的单元格
可在目的单元格C3中输入公式=changedate(b3)即可
其中b3为包含日期的源单元格,再向下拖拉公式即可
|
|