vba 将文本转换为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20375233/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Text to Date?
提问by user1300244
I have a column of dates (column A) stored as text in the format yyyy-mm-ddwhich I'm trying to convert to dates, ultimately so that I can do look ups against them.
我有一列日期（A 列）以yyyy-mm-dd我试图转换为日期的格式存储为文本，最终以便我可以对它们进行查找。
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.
这将单元格的格式更改为日期，但实际上并未更改仍以文本形式存储的值的格式。
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:
我的理解是我需要使用 CDate() 将值的格式从文本更改为日期。我试过这样的事情：
Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
c.Value = CDate(c.Value)
Next c
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.
这给了我一个类型不匹配错误。我想知道这是不是因为我试图将日期值保存回非日期格式的单元格，所以我尝试将它与上面的 .NumberFormat = "date" 结合使用，这也不起作用。
Any suggestions would be appreciated.
任何建议，将不胜感激。
回答by
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.
您可以使用工作表数据的 VBA 等效项快速将类似于日期的文本列转换为实际日期？文本到列命令。
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
Bulk operations are generally much quicker than looping through cells and the VBA's Range.TextToColumns methodis 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 propertyfor a complete list of the available date formats available.
批量操作通常比循环单元格快得多，而且 VBA 的Range.TextToColumns 方法非常快。它还允许您自由设置 MDY 与 DMY 或 YMD 转换掩码，这些掩码会困扰许多日期格式与系统区域设置不匹配的文本导入。有关可用日期格式的完整列表，请参阅TextFileColumnDataTypes 属性。
Caveat: Be careful when importing text that someof 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 methodand specify the xlColumnDataType.
警告：导入某些日期尚未转换的文本时要小心。具有不明确的月份和日期整数的基于文本的日期可能已经被错误地转换了；例如，07/11/2015 可能被解释为 07-Nov-2015 或 11-Jul-2015，具体取决于系统区域设置。在这种情况下，放弃导入并将文本带回 Data ？获取外部数据？从文本并在文本导入向导中指定正确的日期转换掩码。在 VBA 中，使用Workbooks.OpenText 方法并指定 xlColumnDataType。
回答by Sam
You can use DateValueto convert your string to a date in this instance
DateValue在此实例中，您可以使用将字符串转换为日期
Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
c.Value = DateValue(c.Value)
Next c
It can convert yyyy-mm-ddformat string directly into a native Excel date value.
它可以将yyyy-mm-dd格式字符串直接转换为原生 Excel 日期值。
回答by sancho.s ReinstateMonicaCellio
Besides other options, I confirm that using
除了其他选项，我确认使用
c.Value = CDate(c.Value)
works (just tested with the description of your case, with Excel 2010). As for the reasons for you getting a type mismatch error, you may check (e.g.) this.
有效（刚刚使用您的案例描述进行了测试，使用 Excel 2010）。至于您收到类型不匹配错误的原因，您可以检查（例如）this。
It might be a locale issue.
这可能是语言环境问题。
回答by Gary's Student
Perhaps:
也许：
Sub dateCNV()
Dim N As Long, r As Range, s As String
N = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To N
Set r = Cells(i, "A")
s = r.Text
r.Clear
r.Value = DateSerial(Left(s, 4), Mid(s, 6, 2), Right(s, 2))
Next i
End Sub
This assumes that column Acontains text values like 2013-12-25 with no header cell.
这假设列A包含没有标题单元格的文本值，如 2013-12-25。
回答by Jose Luis Martin Cara
This code is working for me
这段代码对我有用
Dim N As Long, r As Range
N = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To N
Set r = Cells(i, "B")
r.Value = CDate(r.Value)
Next i
Try it changing the columns to suit your sheet
尝试更改列以适合您的工作表
回答by Sancarn
I had a very similar issue earlier. Unfortunately I looked at this thread and didn't find an answer which I was happy with. Hopefully this will help others.
我之前有一个非常相似的问题。不幸的是，我看了这个帖子，没有找到我满意的答案。希望这会帮助其他人。
Using VBA.DateSerial(year,month,day)you can overcome Excel's intrinsic bias to US date format. It also means you have full control over the data, which is something I personally prefer:
使用VBA.DateSerial(year,month,day)您可以克服 Excel 对美国日期格式的固有偏见。这也意味着您可以完全控制数据，这是我个人更喜欢的：
function convDate(str as string) as Date
Dim day, month, year as integer
year = int(mid(str,1,4))
month = int(mid(str,6,2))
day = int(mid(str,9,2))
convDate = VBA.DateSerial(year,month,day)
end function
回答by Christian Stanyer
Blast from the past but I think I found an easy answer to this. The following worked for me. I think it's the equivalent of selecting the cell hitting F2 and then hitting enter, which makes Excel recognize the text as a date.
来自过去的爆炸，但我想我找到了一个简单的答案。以下对我有用。我认为这相当于选择单元格按 F2 然后按 Enter，这使 Excel 将文本识别为日期。
Columns("A").Select
Selection.Value = Selection.Value
回答by Eric
To the OP... I also got a type mismatch error the first time I tried running your subroutine. In my case it was cause by non-date-like data in the first cell (i.e. a header). When I changed the contents of the header cell to date-style txt for testing, it ran just fine...
对于 OP... 我第一次尝试运行您的子程序时也遇到了类型不匹配错误。在我的情况下，它是由第一个单元格（即标题）中的非日期类数据引起的。当我将标题单元格的内容更改为 date-style txt 进行测试时，它运行得很好......
Hope this helps as well.
希望这也有帮助。
回答by Vitaliy Prushak
I've got rid of type mismatch by following code:
我通过以下代码摆脱了类型不匹配：
Sub ConvertToDate()
Dim r As Range
Dim setdate As Range
'in my case I have a header and no blank cells in used range,
'starting from 2nd row, 1st column
Set setdate = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
With setdate
.NumberFormat = "dd.mm.yyyy" 'I have the data in format "dd.mm.yy"
.Value = .Value
End With
For Each r In setdate
r.Value = CDate(r.Value)
Next r
End Sub
But in my particular case, I have the data in format "dd.mm.yy"
但在我的特殊情况下，我的数据格式为“dd.mm.yy”
回答by Gilco
For DD-MM-YYYY here is a simple workaround to manage string and dates:
对于DD- MM-YYYY，这里有一个简单的解决方法来管理字符串和日期：
insert the date into the string via DD-MMM-YYYY for example 01-11-2017 -> 01-Nov-2017
通过 DD- MMM-YYYY将日期插入字符串，例如 01-11-2017 -> 01-Nov-2017
U can use the FORMAT(date, "dd-mmm-yyyy") to input dates into a string from the spread sheet.
您可以使用 FORMAT(date, "dd- mmm-yyyy") 将日期输入到电子表格中的字符串中。
Later, when you output it from a string, it will not confuse the days and months.
以后从字符串中输出时，不会混淆天数和月数。
