将文字转换为日期? [英] Convert Text to Date?

问题描述

我有一列日期(列A)以文本格式存储为yyyy-mm-dd，我正尝试将其转换为日期，以便最终可以对它们进行查找.

I have a column of dates (column A) stored as text in the format yyyy-mm-dd which I'm trying to convert to dates, ultimately so that I can do look ups against them.

我已经在这里阅读了一些主题，并尝试了一些建议，但是我什么都做不了.一个正在使用:

I've read a few topics on here and tried some of the suggestions, but I can't get anything to work. One was using:

Columns("A").Select
Selection.NumberFormat = "date"

这更改了单元格的日期格式，但实际上并未更改仍存储为文本的值的格式.

This changed the format of the cells to date but didn't actually change the format of the value which was still stored as text.

我的理解是，我需要使用CDate()将值的格式从文本更改为日期.我已经尝试过这样的事情:

My understanding is that I need to use CDate() to change the format of the value from text to date. I've tried something like this:

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value = CDate(c.Value)
Next c

这给了我一个类型不匹配的错误.我想知道这是否是因为我试图将日期值保存回非日期格式的单元格中，所以我尝试将其与上述.NumberFormat ="date"组合使用，这也不起作用.

Which gives me a type mismatch error. I wondered if this was because I was trying to save date values back into a non-date formatted cell so I tried combining it with the .NumberFormat = "date" above, which didn't work either.

任何建议将不胜感激.

推荐答案

您可以使用与工作表的数据"►文本到列"命令等效的VBA，将类似于日期的文本列快速转换为实际日期.

You can quickly convert a column of text that resembles dates into actual dates with the VBA equivalent of the worksheet's Data ► Text-to-Columns command.

With ActiveSheet.UsedRange.Columns("A").Cells
    .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
    .NumberFormat = "yyyy-mm-dd"   'change to any date-based number format you prefer the cells to display
End With

批量操作通常比在单元格和VBA的 Range.TextToColumns方法中循环要快得多 Range.TextToColumns方法非常快.它还使您可以自由设置MDY与DMY或YMD转换掩码，该掩码在日期格式与系统的区域设置不匹配的情况下困扰许多文本导入.请参阅 TextFileColumnDataTypes属性，以获取可用的完整列表.日期格式可用.

Bulk operations are generally much quicker than looping through cells and the VBA's Range.TextToColumns method is very quick. It also allows you the freedom to set a MDY vs. DMY or YMD conversion mask that plagues many text imports where the date format does not match the system's regional settings. See TextFileColumnDataTypes property for a complete list of the available date formats available.

注意事项:导入日期中 some 尚未转换的文本时要小心.具有不明确的月和日整数的基于文本的日期可能已经错误地转换；例如根据系统区域设置，2015年7月11日可能被解释为2015年11月7日或2015年7月11日.在这种情况下，请放弃导入，并使用数据"►获取外部数据"►来自文本"将文本重新带回，并在文本导入"向导中指定正确的日期转换掩码.在VBA中，使用 Workbooks.OpenText方法并指定xlColumnDataType.

Caveat: Be careful when importing text that some of the dates have not already been converted. A text-based date with ambiguous month and day integers may already been converted wrongly; e.g. 07/11/2015 may have been interpreted as 07-Nov-2015 or 11-Jul-2015 depending upon system regional settings. In cases like this, abandon the import and bring the text back in with Data ► Get External Data ► From Text and specify the correct date conversion mask in the Text Import wizard. In VBA, use the Workbooks.OpenText method and specify the xlColumnDataType.

这篇关于将文字转换为日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！