将文本转换为日期? [英] 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.

如有任何建议，我们将不胜感激.

Any suggestions would be appreciated.

推荐答案

您可以使用 DateValue 在此实例中将您的字符串转换为日期

You can use DateValue to convert your string to a date in this instance

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

它可以将yyyy-mm-dd格式字符串直接转换为原生Excel日期值.

It can convert yyyy-mm-dd format string directly into a native Excel date value.

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