|
'主程序
Sub conversionDate()
Dim c, arr, i, x
'1)初始化
If Application.FindFile = False Then End
c = InputBox("日期数据在第几列:", "输入", 1)
If c <> "" Then c = c * 1 Else End
arr = Range(Cells(5, c), Cells(Cells(Rows.Count, c).End(3).Row, c))
'2)转换
For i = 1 To UBound(arr)
x = arr(i, 1)
If Len(x) Then
If x = val(x) Then x = Number(x) Else x = notNumber(x)
arr(i, 1) = IIf(x = 0, "", x)
End If
Next i
'3)输出
Columns(c + 1).NumberFormatLocal = "yyyy/m/d"
Cells(5, c + 1).Resize(10000) = ""
Cells(5, c + 1).Resize(i - 1) = arr
End Sub
'数值型
Function Number(x) As Date
Select Case Len(x)
Case 5
Select Case Right(x, 1) * 1
Case 1 To 9
Number = Format(x, "0000/0")
Case Else
Number = 0 '不处理
End Select
Case 6
Select Case Right(x, 2) * 1
Case 1 To 11
Number = Format(x, "0000/00")
Case 13 To 99
Number = Format(x, "0000/0/0")
Case Else
Number = 0 '不处理
End Select
Case 7
Select Case Mid(x, 5, 2) * 1
Case 1 To 9
Number = Format(x, "0000/00/0")
Case 11, 12
Number = 0 '不处理
Case 13 To 99
'>>> 还需判断最后2位。比如2016132
Select Case Right(x, 2) * 1
Case 1 To 31
Number = Format(x, "0000/0/00")
Case Else
Number = 0 '不处理
End Select
'<<<
Case Else
Number = 0 '不处理
End Select
Case 8
Number = Format(x, "0000/00/00")
Case Else
Number = 0 '不处理
End Select
End Function
'非数值型
Function notNumber(x) As Date
Dim arr, i
x = VBA.Replace(x, "日", "")
arr = Array("年", "月", ".", ",", "。", "、", " ")
For i = 0 To UBound(arr)
x = VBA.Replace(x, arr(i), "/")
Next i
arr = Array("○", "一", "二", "三", "四", "五", "六", "七", "八", "九", "十")
For i = 0 To UBound(arr)
x = VBA.Replace(x, arr(i), i)
Next i
arr = Array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖", "拾")
For i = 0 To UBound(arr)
x = VBA.Replace(x, arr(i), i)
Next i
notNumber = IIf(VBA.IsDate(x), x, 0)
End Function
规范日期格式2.rar
(23.47 KB, 下载次数: 15)
|
|