在日常录入EXCEL表格的单元格里 ，我们输入一些一般性的日期内容，如：2017-10-17 或 2017/10/17时，EXCEL会自动识别为日期并按单元格设计格式显示，单元格中存储的值也是日期格式的值。
但我们进行不规范的日期输入时，如在单元格中输入：2017.10.10或2017。10。10或20171010时，EXCEL不会自动识别这些内容为日期，而只会识别为字符串（文本），且在单元格中存储的也只是文本而已。
我们通过编写VBA代码可以轻松解决此问题，让EXCEL完美识别输入的任何日期内容，不管是20171010或2017.10.10还是201711或20170101或2017131都可被正确识别，而且是直接将单元格中存储的值转换为日期值，不仅仅是显示格式的转换。
闲话不说，直接让VBA代码：
(要录入VBA代码，必须通过EXCEL进入VBA编辑器，这部分内容可搜索下)
'以下代码都要放到一个sheet的类模块之中Dim nDatePrivate Sub Worksheet_Activate()'加载sheet的事件 nDate = InputBox("请确定此工作表中第几列为日期型的数据！", "输入数字", "2") If nDate = "" Then nDate = 2 '--只操作指定的列号的列，目前只操作B列（第2列） Else nDate = Val(nDate) End IfEnd SubPrivate Sub Worksheet_Change(ByVal Target As Range)'--sheet中任何地方发生数据改变时触发的事件 If nDate = 0 Then Exit Sub If Target.Cells.Column = nDate Then '--如果是第二列才检验文本为日期 Target.Value = TryChangeDate2(Target.Value) End IfEnd SubPublic Function TryChangeDate2(ByVal strDATEcome As String) As Variant On Error GoTo TryChangeDate2ERR Dim strDATE As String strDATE = Trim(strDATEcome) Dim myDate As Date Dim strK As String strK = mTrim(strDATEcome) Dim k As Integer, nkkkk As Integer k = -1k0: k = 0 myDate = DateValue(strDATE) myDate = Format(myDate, "yyyy/m/d") TryChangeDate2 = myDate Exit Functionk1: k = 1 myDate = DateValue(strDATE) myDate = Format(myDate, "yyyy/m/d") TryChangeDate2 = myDate Exit FunctionTryChangeDate2ERR: Err.Clear If k = 0 Then nkkkk = Len(strK) Select Case nkkkk Case 4 If InStr(1, strK, ".") = 0 And InStr(1, strK, ",") = 0 And InStr(1, strK, "/") = 0 And InStr(1, strK, "\") = 0 And InStr(1, strK, "-") = 0 Then strDATE = Left(strK, 2) & "/" & Mid(strK, 3, 1) & "/" & Mid(strK, 4, 1) End If Case 5 If InStr(1, strK, ".") = 0 And InStr(1, strK, ",") = 0 And InStr(1, strK, "/") = 0 And InStr(1, strK, "\") = 0 And InStr(1, strK, "-") = 0 Then If Val(Mid(strK, 3, 1)) >= 3 Then strDATE = Left(strK, 2) & "/" & Mid(strK, 3, 1) & "/" & Mid(strK, 4, 2) Else strDATE = Left(strK, 2) & "/" & Mid(strK, 3, 2) & "/" & Mid(strK, 5, 1) End If End If Case 6 If InStr(1, strK, ".") = 0 And InStr(1, strK, ",") = 0 And InStr(1, strK, "/") = 0 And InStr(1, strK, "\") = 0 And InStr(1, strK, "-") = 0 Then If Left(strK, 1) = "1" Or Left(strK, 1) = "2" Then strDATE = Left(strK, 4) & "/" & Mid(strK, 5, 1) & "/" & Mid(strK, 6, 1) Else strDATE = Left(strK, 2) & "/" & Mid(strK, 3, 2) & "/" & Mid(strK, 5, 2) End If GoTo theEnd End If strDATE = Left(strK, 2) & "/" & Mid(strK, 4, 1) & "/" & Mid(strK, 6, 1) Case 7 If InStr(1, strK, ".") = 0 And InStr(1, strK, ",") = 0 And InStr(1, strK, "/") = 0 And InStr(1, strK, "\") = 0 And InStr(1, strK, "-") = 0 Then If Val(Mid(strK, 5, 1)) >= 3 Then strDATE = Left(strK, 4) & "/" & Mid(strK, 5, 1) & "/" & Mid(strK, 6, 2) Else strDATE = Left(strK, 4) & "/" & Mid(strK, 5, 2) & "/" & Mid(strK, 7, 1) End If Else If Val(Mid(strK, 4, 1)) >= 3 Then strDATE = Left(strK, 2) & "/" & Mid(strK, 4, 1) & "/" & Mid(strK, 6, 2) Else strDATE = Left(strK, 2) & "/" & Mid(strK, 4, 2) & "/" & Mid(strK, 7, 1) End If End If Case 8 If InStr(1, strK, ".") = 0 And InStr(1, strK, ",") = 0 And InStr(1, strK, "/") = 0 And InStr(1, strK, "\") = 0 And InStr(1, strK, "-") = 0 Then strDATE = Left(strK, 4) & "/" & Mid(strK, 5, 2) & "/" & Mid(strK, 7, 2) Else strDATE = Left(strK, 4) & "/" & Mid(strK, 6, 1) & "/" & Mid(strK, 8, 1) End If Case 9 If Val(Mid(strK, 6, 1)) >= 3 Then strDATE = Left(strK, 4) & "/" & Mid(strK, 6, 1) & "/" & Mid(strK, 8, 2) Else strDATE = Left(strK, 4) & "/" & Mid(strK, 6, 2) & "/" & Mid(strK, 9, 1) End If Case 10 strDATE = Left(strK, 4) & "/" & Mid(strK, 6, 2) & "/" & Mid(strK, 9, 2) End SelecttheEnd: GoTo k1 End If TryChangeDate2 = strDATEcomeEnd FunctionPublic Function mTrim(ByVal strCome As String) As String'--此函数的作用是去掉字符串中间的空格On Error GoTo mTrimErr Dim i As Integer, j As Integer Dim strLS As String, k As String * 1, strResult As String strLS = Trim(strCome) strResult = "" j = Len(strLS) For i = 1 To j k = Mid(strLS, i, 1) If k <> " " And k <> " " And VarType(k) <> vbNull And k <> vbNullString Then strResult = strResult & k End If Next mTrim = strResult Exit FunctionmTrimErr: Err.Clear mTrim = strComeEnd Function'---以上代码可实现在EXCEL指定列（上面指定为B列）中录入日期内容时，任意可识别的日期都会被自动转换成标准日期值，并以日期值存储在单元格中'---欢迎大家批评指正，如果发现错误，欢迎指正，如有不明子的地方，欢迎交流'--QQ: 578652607
人，也总是很难发现自己的错误，
