vba 如何将日期字符串与 Excel 中的日期进行转换和比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6877027/
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
How to convert and compare a date string to a date in Excel
提问by Gidon Wise
= "7/29/2011 12:58:00 PM" > NOW()
I'd like this expression to return FALSE and yet it returns TRUE.
我希望这个表达式返回 FALSE，但它返回 TRUE。
I know I can break apart my datetime into a date and a time and add them together as follows:
我知道我可以将日期时间分解为日期和时间，然后将它们加在一起，如下所示：
= DateValue("7/29/2011") + TimeValue("12:58:00 PM") > NOW()
But, this seems inelegant to me. I want a simple function or approach that looks nice and I feel certain that it's out there but I just can't find it.
但是，这对我来说似乎不雅。我想要一个看起来不错的简单函数或方法，我确信它就在那里，但我就是找不到。
I also know there is a VBA function called CDatewhich can typecast the string into a datetime and that would be perfect. But, I don't see how to call a VBA function in an excel cell.
我也知道有一个 VBA 函数被调用CDate，它可以将字符串类型转换为日期时间，这将是完美的。但是，我不知道如何在 Excel 单元格中调用 VBA 函数。
回答by Tiago Cardoso
Multiply the string by one and the comparison function will work:
将字符串乘以一，比较函数将起作用：
= 1*"7/29/2011 12:58:00 PM" > NOW()
The answer to your question is tightly related to @Jean-Fran?ois's comment: Why is the date being interpreted by Excel as a Text and not by a date?
您问题的答案与@Jean-Fran?ois 的评论密切相关：为什么 Excel 将日期解释为文本而不是日期？
Once you find it out, you'll be able to do the comparison.
一旦你找到它，你就可以进行比较。
If that's because the string is being retrieved as a text, you can simply multiply it by one and the comparison function will work, then. But it applies only in case the string format is a valid date/time format in your regional settings.
如果那是因为字符串被作为文本检索，您可以简单地将其乘以 1，然后比较函数将起作用。但它仅适用于字符串格式在您的区域设置中是有效日期/时间格式的情况。
回答by jonsca
You could wrap the VBA call in a custom function:
您可以将 VBA 调用包装在自定义函数中：
Function ReturnDate(ByVal datestr As String) As Date
ReturnDate = CDate(datestr)
End Function
which you can use just like a formula in your sheet.
您可以像工作表中的公式一样使用它。
回答by Jean-Fran?ois Corbett
I'm upgrading the following from a comment to an answer:
我正在将以下内容从评论升级为答案：
Unless you have a very specific reason to do so (and right now I can't think of any), dates (and other values) really shouldn't be "hard-coded" in cells as strings like you show. Hard-coding the string like that makes it invisible and inflexible. The user will just see TRUEor FALSEwith no indication of what this means.
除非你有一个非常具体的理由这样做（现在我想不出任何理由），日期（和其他值）真的不应该像你展示的那样在单元格中“硬编码”为字符串。像这样对字符串进行硬编码使其不可见且不灵活。用户只会看到TRUE或FALSE不知道这意味着什么。
I would just put your date 7/29/2011 12:58:00 PMin a cell on its own e.g. A1, and set the cell's format to some date format. Then you can say = A1 > NOW().
我只是将您的日期7/29/2011 12:58:00 PM放在一个单独的单元格中，例如 A1，并将单元格的格式设置为某种日期格式。那你可以说= A1 > NOW()。
Contrary to @jonsca's and @Tiago Cardoso's answers, this answer doesn't address your specific question, but then again, what you are asking seems like really bad practice to me!
与@jonsca 和@Tiago Cardoso 的答案相反，这个答案没有解决您的具体问题，但话说回来，您所问的问题对我来说似乎是非常糟糕的做法！
回答by Chris Flynn
The simplest way to do this is to make a VBA function that uses CDATE and return your comparison. Then, call the function from an excel cell.
最简单的方法是创建一个使用 CDATE 的 VBA 函数并返回您的比较。然后，从 Excel 单元格调用该函数。
The VBA Function
VBA 函数
Public Function compareDate(ByVal inputDate As String) As Boolean
compareDate = CDate(inputDate) > Now()
End Function
Then in your spreadsheet, just do
然后在您的电子表格中，只需执行
=compareDate("YOUR DATE")
The function will return "FALSE" if it is older and "TRUE" if it is newer than Now()
如果它比 Now() 旧，则该函数将返回“FALSE”，如果它比 Now() 新，则返回“TRUE”
