= "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 CDate which 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.
我也知道有一个名为CDate的VBA函数可以将字符串强制转换为日期时间，这将是完美的。但是，我没有看到如何在excel单元格中调用VBA函数。
4 个解决方案
#1
4
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 TRUE or FALSE with no indication of what this means.
除非你有一个非常具体的理由这样做（现在我想不出任何），日期（和其他值）实际上不应该像单元格那样在单元格中“硬编码”。对字符串进行硬编码会使其变得不可见且不灵活。用户只会看到TRUE或FALSE，但没有表明这意味着什么。
I would just put your date 7/29/2011 12:58:00 PM in 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的答案相反，这个答案并没有解决你的具体问题，但话说回来，你所问的对我来说似乎真的很糟糕！
#2
7
Multiply the string by one and the comparison function will work:
将字符串乘以1，比较函数将起作用：
= 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，然后比较函数就可以了。但它仅适用于字符串格式是区域设置中的有效日期/时间格式的情况。
#3
5
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.
您可以像工作表中的公式一样使用它。
#4
3
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()
如果它比较旧，该函数将返回“FALSE”，如果它比Now（）更新则返回“TRUE”
#1
4
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 TRUE or FALSE with no indication of what this means.
除非你有一个非常具体的理由这样做（现在我想不出任何），日期（和其他值）实际上不应该像单元格那样在单元格中“硬编码”。对字符串进行硬编码会使其变得不可见且不灵活。用户只会看到TRUE或FALSE，但没有表明这意味着什么。
I would just put your date 7/29/2011 12:58:00 PM in 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的答案相反，这个答案并没有解决你的具体问题，但话说回来，你所问的对我来说似乎真的很糟糕！
#2
7
Multiply the string by one and the comparison function will work:
将字符串乘以1，比较函数将起作用：
= 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，然后比较函数就可以了。但它仅适用于字符串格式是区域设置中的有效日期/时间格式的情况。
#3
5
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.
您可以像工作表中的公式一样使用它。
#4
3
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()
如果它比较旧，该函数将返回“FALSE”，如果它比Now（）更新则返回“TRUE”