Excel中的日期未识别为日期

Question

当我打开一个包含日期列的文件时，Excel无法将这些单元格识别为日期。单元格中的日期如下: 31-Mar-16 23:51

我已经尝试使用text-to-column和datevalue，尽管这并不能解决问题。我还读了这篇文章：

String was not recognized as a valid date time

我仍然有这个问题。

该文件来自英国。我的计算机区域设置为语言为荷兰语，键盘为美国。即使当我将我的区域设置为UK并重新打开文件时，日期仍然无法识别。Excel将其视为文本。

通过一些文本编辑，我可以将31- Mar -16 23:51替换为31-Mar-16，然后将Mar替换为3。在这种情况下，问题就解决了，尽管这种方法效率不高，因为我会定期接收这些文件。

有人知道解决这个问题的更好方法吗？

谢谢!

Answer 1

我怀疑由于区域设置的原因，无法识别月份缩写。如果是这种情况，请选择不正常的单元格并运行此宏：

Sub DateFixer()
    Dim r As Range, t As String
    Dim dy As Long, mont As Long, yr As Long, hr As Long, minn As Long
    For Each r In Selection
        t = r.Text

        ary = Split(t, " ")
        bry = Split(ary(0), "-")
        cry = Split(ary(1), ":")

        dy = CLng(bry(0))
        mont = mnth(bry(1))
        yr = 2000 + CLng(bry(2))

        hr = CLng(cry(0))
        minu = CLng(cry(1))
        r.NumberFormat = "General"
        r.Value = DateSerial(yr, mont, dy) + TimeSerial(hr, minu, 0)
    Next r
End Sub

Public Function mnth(st) As Long
    Dim sMth

    sMth = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    With Application.WorksheetFunction
         mnth = .Match(st, sMth, 0)
    End With
End Function

请注意，只需更改UDF()中的数组，宏就可以适应任何语言的缩写。

Answer 2

要在没有VBA的情况下转换月份，您可以使用=FIND()和=MID()函数，如下所示...

​

​

进一步使用=LEFT()、=MID()、=RIGHT()来分解您的源日期，并根据您的规则组成一个正确的日期。

另请参阅Excel using date/time with multiple different region formats. VB? Formulas?