如何将日期字符串转换并比较Excel中的日期 [英] How to convert and compare a date string to a date in Excel

问题描述

  =7/29/2011 12:58:00 PM> NOW（）
  

我希望这个表达式返回FALSE，但它返回TRUE。 >

我知道我可以将日期时间分解成日期和时间，并将它们添加在一起如下：

  = DateValue（7/29/2011）+ TimeValue（12:58:00 PM）> NOW（）
  

但是，这似乎对我来说不合适。我想要一个简单的功能或方法看起来不错，我觉得它在那里，但我找不到它。

我也知道有一个VBA功能称为 CDate ，可以将字符串类型转换为日期时间，这将是完美的。但是，我没有看到如何在excel单元格中调用VBA函数。

解决方案

我正在升级以下内容对答案的评论：

除非你有一个非常具体的理由这样做（现在我不能想到任何），日期（和其他值）在细胞中真的不应该像你所显示的字符串那样被硬编码。硬编码这样的字符串使它看不见和僵化。用户将只看到 TRUE 或 FALSE ，不表示这是什么意思。

我只是把它的日期 7/29/2011 12:58:00 PM 自己的A1，并将单元格的格式设置为某种日期格式。然后你可以说 = A1> NOW（）。

与@ jonsca和@Tiago Cardoso的答案相反，这个答案没有解决你的具体问题，但是再一次，你所问的是对我来说真的很糟糕的做法！

= "7/29/2011 12:58:00 PM" > NOW()

I'd like this expression to return FALSE and yet it returns 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.

解决方案

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.

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().

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!

这篇关于如何将日期字符串转换并比较Excel中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！