Unable to get Excel to recognize date in column

问题：问题是： Excel不想将日期识别为日期，即使你通过 “单元格格式–数字–自定义 "来告诉它这些是”mm/dd/yyyy “的日期。正如你所知道的，当excel识别出日期时，它会进一步将其存储为一个数字–例如”41004“，但根据你指定的格式显示为日期。为了增加混乱，excel可能只转换部分日期，如08/04/2009，但留下其他如07/28/2009未转换的日期。在 "数据 "下选择 "文本到列 "按钮。在第一个屏幕上留下单选按钮 ”delimited“，然后点击Next。取消点击任何一个分界线框（任何一个框空白；没有复选标记），然后点击Next。在列数据格式下选择日期，在相邻的组合框中选择MDY，然后点击Finish。现在你得到了日期值（也就是说，Excel已经将你的值识别为Date数据类型），但格式化很可能仍然是本地日期，而不是你想要的mm/dd/yyyy。选择英文(US)。这将给你一个类似”m/d/yy “的格式。然后你可以选择 "自定义"，在那里你可以输入”mm/dd/yyyy “或者从自定义字符串列表中选择这个。从Patrick的帖子中粘贴数据后，选择粘贴特殊（Ctrl+Shift+V）并选择未格式化文本。这将打开 "导入文本 "对话框。字符集仍然是Unicode，但语言选择英文(美国)；你还应该勾选 "检测特殊数字"。你的日期会立即以默认的美国格式出现，并可进行日期排序。如果你希望使用特殊的美国格式MM/DD/YYYY，你需要在 "格式化单元格 "中指定一次–在粘贴前或粘贴后。不幸的是，只有通过上面的步骤1，我才能够让Excel识别这些文本字符串为日期。很显然，如果你经常这样做，那就会很麻烦，你可能会用一个可视化的基本程序，只需按一下按钮就可以完成。这意味着在格式化为数字（或日期）的单元格中，有一个文本字符串。你可以说 - 前导号使电子表格无法识别这个数字。你需要知道在公式栏中寻找这个问题 - 因为电子表格只是显示一个看起来像左对齐的数字。要处理这个问题，选择你要更正的列，在菜单中选择Data | Text to Columns，然后点击确定。有时你可以指定数据类型，但如果你之前已经将列的格式设置为你的特定数据类型–你就不需要了。这个命令的真正目的是用分隔符将一个文本列一分为二或更多，但它对这个问题也很有效。我在Libreoffice中测试过，但在Excel中也有同样的菜单项。

选择所有列，进入定位和替换，将"/"替换为/即可。

这可能与原提问者没有关系，但可能对其他无法对一列日期进行排序的人有帮助。

我发现Excel无法识别一列日期都是1900年以前的日期，坚持认为它们是一列文字（因为1/1/1900有数字等价物1，而负数显然是不允许的）。所以我对所有的日期（19世纪的日期）做了一个总体替换，把它们放到1900年，例如180 -> 190，181 -> 191等。然后，排序过程很顺利。最后，我又做了一个替换，例如190 -> 180。

希望这对其他历史学家有帮助。

我在处理一个类似的问题，从SAP数据库中提取了数千条记录，在同一日期列中出现了两种不同的日期格式（大部分是标准的 “YYY-MM-DD"，但约10%是 "MM-DD-YY"）。每个月手动编辑650行不是一个选项。

上面的选项没有一个（0………..0………0……….零）起作用。是的，我都试过了。复制到文本文件或明确导出为txt，不知为何，仍然对10%的日期的格式（或缺乏格式）没有任何影响，只是坐在角落里拒绝表现。

我唯一能解决这个问题的方法是在错误的日期列的右边插入一个空白的列，然后使用下面的公式，相当简单的公式：

(假设你的错误数据是在Column D中)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))*

然后我可以把结果复制到我的新的，计算出来的列中，在错误的日期上面粘贴 "Values"，然后我可以删除我的计算列。

我在excel中的日期问题上也遇到了同样的问题。格式符合excel中的日期要求，但如果不编辑每个单元格，它将无法识别日期，当你处理成千上万的行时，手动编辑每个单元格是不实际的。解决的办法是在日期行上运行查找和替换，将连字符替换成连字符。Excel运行通过列替换像与像，但其结果是，它现在重新调整了日期! 解决了!

看来Excel并没有将日期识别为日期，而是将其识别为文本，因此，你得到的排序选项是A到Z，如果你的操作正确，你应该会得到这样的结果：

因此，确保Excel识别日期很重要。最简单的方法是使用快捷键CTRL+SHIFT+3。

我是这样做的。我只是把你上面的帖子复制过来，然后粘贴到excel中。然后我应用了上面的快捷方式，就得到了需要的排序选项。查看图片

我怀疑是Excel无法理解格式的问题…… 虽然你选择了日期格式，但仍然是文本格式。

你可以简单地测试一下。当你尝试更新日期格式时，选择该列，然后右键点击它，选择格式化单元格（就像你已经做的那样），但选择2001-03-14选项（靠近列表底部）。然后查看你的单元格的格式。

我会怀疑只有一些日期格式更新正确，说明Excel仍然把它当作一个字符串，而不是日期（注意下面的屏幕截图中的不同格式）！

有解决这个问题的方法，但是，没有一个会因为你每次导出的时候都是自动的。我建议使用VBa，在这里你可以简单地运行一个宏，将美国的日期格式转换为英国的日期格式，但是，这意味着每次都要复制和粘贴VBa到你的工作表。我将假设导出的Exchange Excel文件将始终具有相同的文件名/目录，并提供了一个工作示例：

所以，创建一个新的Excel表，名为ImportedData.xlsm（excel启用）。这就是我们要导入导出的Exchange Excel文件的文件！

将这个VBa添加到ImportedData.xlsm文件中

Sub DoTheCopyBit()

Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)

Do While fileName <> ""

'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)

Workbooks(fileName).Worksheets("Sheet1").Copy _

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")

     Dim splitty() As String
     splitty = Split(Range(dateCol & row).Value, "/")
     Range(dateCol & row).NumberFormat = "@"
     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
     row = row + 1
Loop

End Sub

我还做了什么，就是将日期格式更新为yyyyy-mm-dd，因为这样一来，即使Excel给你的排序过滤器是A-Z，而不是最新的值，它仍然可以工作

我肯定上面的代码有错误，但我显然不知道你有什么类型的数据，但我用单列的日期测试了一下，它对我来说很好！ 如何在MS Office中添加VBA？

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

这里有一个简单的解决方案–创建新的列，使用=datevalue(cell)公式，然后将公式复制到你的其他行中–几秒钟就可以解决。

我想通了！

在日期的开头和结尾处有一个空格。

1.如果你用查找和替换，然后按空格键，是不会成功的。你必须在月号前点击右键，按shift和左箭头选择并复制。 3.有时你需要用鼠标选择空格。然后在查找和替换中粘贴这个空格，所有的日期都会变成日期。 4.如果有空格和日期，选择数据>去数据>文本到数据>列>分隔符>空格，然后完成。

我对以上所有的建议都没有什么好结果 - 想出了一个非常简单的解决方案，效果很好。将数据粘贴到Google Sheets中，高亮显示日期列，点击格式，然后点击数字，再点击数字，再一次将其改为数字格式。然后我将数据复制粘贴到Excel电子表格中，点击日期，然后将单元格格式化为日期。很快就完成了。希望这样做能帮助到你。

我只是简单地复制了数字1（1），然后用PASTE SPECIAL函数将其乘以日期列（数据）。然后，它变成了通用格式化 **，即42102*，然后在格式化上应用日期，它现在识别为日期。

我在英国的解决方法–我的日期中带点的日期是这样的：

03.01.17

&001

我用以下方法解决了这个问题：

1.高亮显示整个列。进入查找并选择/替换。 3. 我把所有的句号都换成了中间的破折号，例如03.01.17 03-01-01-17。 5.格式化单元格，数字选项卡选择日期。 6.使用类型14-03-12(我的中间破折号是必不可少的) 7.Locale English (英国)

当排序时，所有的日期都会被排序。

分享一个有点长，但更简单的解决方案………..*不需要运行宏或怪胎的东西……….简单的ms excel公式和编辑。 - 因此，为了使日期格式对称，我们创建了额外的列，将 “混合格式 "的日期列转换成TEXT。- 最后，从我们创建的字符串中，我们通过日期公式将这些字符串转换为日期。

我尝试了各种建议，但发现对我来说最简单的解决方案如下…..

参见图1和图2的例子…… (注意–有些字段被故意隐藏了，因为它们对例子没有任何帮助)。我希望这对你有帮助…..

1. 字段C—-一个混合格式，让我绝对疯狂。这就是数据直接从数据库中来的方式，没有多余的空格或疯狂的字符。当以文本形式显示时，01/01/2016显示为 “42504 "类型的值，与04/15/2006混合显示为 "42504"。字段F–我在这里得到了字段C的长度（根据日期格式的不同，LEN公式会是10中5的长度）。为了简单起见，该字段为通用格式。

2. 字段G–获取混合日期中的月份分量–根据字段F中的长度结果（5或10），我要么从字段C中的日期值中获取月份，要么获取字符串中的月份字符。字段H–获取混合日期的日分量–根据字段F中的长度结果（5或10），我要么从字段C中的日期值中获取日期，要么从字符串中获取日期字符。 图片1：基本电子表格，显示值和字段名称 图片2：电子表格，显示与上面的解释相匹配的公式

希望对你有帮助。

这个问题让我抓狂，然后我偶然发现了一个简单的解决办法。至少它对我的数据起了作用。它很容易做，也很好记。但是，我不知道为什么这样做能起作用，但是按照上面的方法改变类型就不行。1. 选择有日期的列 2. 在你的日期中搜索一个年份，例如2015年。选择替换为相同年份的全部，2015年。3. 3. 每一年重复一次。这样就把类型改成了实际的日期，逐年更换。 如果你的数据中有很多年份，这个还是比较麻烦。比较快的是搜索并将201替换为201（替换为2010年到2019年）；将200替换为200（替换为2000年到2009年）；以此类推。

选择日期并运行此代码…..

On Error Resume Next
    For Each xcell In Selection            
        xcell.Value = CDate(xcell.Value)     
    Next xcell                              
End Sub

我试图在一个下载的账户交易电子表格上对 “日期 "进行排序。

我无法解释为什么不同的浏览器会影响数据的格式化，但很明显，这是个非常快速的解决方案。

如果Excel顽固地拒绝识别你的列为日期，可以用另一个列来替换：

• 在旧列的右侧添加一个新列
• 右键点击新列，选择Format
• 设置格式为date
• 高亮显示整个旧列并复制
• 高亮显示新列的顶部单元格，选择Paste Special，然后只粘贴values
• 现在可以删除旧列了。

这种情况在Excel中不同地域之间交换日期数据时经常发生。如果你可以控制导出数据的VBA程序，我建议你导出代表日期的数字而不是日期本身。这个数字与单一的日期具有唯一的相关性，无论格式和地域如何，都是唯一的。例如，CSV文件中显示的是24/09/2010，而不是24/09/2010，它将显示40445。

在导出循环中，当你保留每个单元格时，如果是一个日期，使用CLng(myDateValue)转换为数字。这是我的一个例子，我的循环运行了一个表的所有行，并导出为CSV（注意，我也用一个标签代替了字符串中的逗号，我在导入时也是用一个标签来代替，但你可能不需要这样做）：

arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i

我用的是mac。 进入excel首选项>编辑>日期选项>自动转换日期系统。

我通常只是创建一个额外的列来进行排序或合计，所以使用year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1)。

这将提供一个可以排序的yyyymmdd结果。使用len(a1)只是允许为1-9月份增加一个额外的零。