vba Excel宏，如何确定日期是否过去？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14126004/
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
Excel macro, how to determine if a date is in the past?
提问by HopelessN00b
Pardon the basicness of the question, but I figure there has to be a simple, naive solution to this that I just can't find.
请原谅这个问题的基本性，但我认为必须有一个我找不到的简单，天真的解决方案。
The basic scenario is that I have an Excel workbook that we use as our corporate solution for tracking backup success rates, and as a result, we have a number of sheets with the date in the first column, and some small macros to do calculations, based on whether the date is in the past or not. (Mainly for the managers who look at this sheet and don't want a bunch of empty rows, so the macros either hide or reveal the appropriate rows.)
基本情况是我有一个 Excel 工作簿，我们将其用作跟踪备份成功率的公司解决方案，因此，我们有许多工作表，第一列中有日期，还有一些小宏来进行计算，基于日期是否在过去。（主要针对查看此工作表并且不想要一堆空行的经理，因此宏要么隐藏或显示适当的行。）
This has been working well until yesterday (happy new year); I assume because the macros are doing a string comparison, rather than a date comparison. (01/01/2013is smaller than 12/31/2012, when viewed as strings.)
这一直运行良好，直到昨天（新年快乐）；我假设是因为宏正在进行字符串比较，而不是日期比较。(01/01/2013小于12/31/2012，当被视为字符串时。)
The macros are below, and the question is essentially if there's a native way to compare dates in VBA, or if I need to convert the dates from a human date format into yyyy/mm/ddfirst, and then do my logic. (And if I do need to do so, a basic how would be nice - I try to avoid Excel and VBA in general where ever possible, so I'm extremely limited in my coding skills as far as VBA goes.)
宏在下面，问题基本上是在 VBA 中是否有比较日期的本机方法，或者我是否需要yyyy/mm/dd先将日期从人类日期格式转换为日期，然后再执行我的逻辑。（如果我确实需要这样做，一个基本的方法会很好 - 我尽量避免使用 Excel 和 VBA，所以就 VBA 而言，我的编码技能非常有限。）
A2would be the cell with the first date we started using this new version of the spreadsheet, and A454would be the last date I've bothered to extend the spreadsheet to, corresponding to the end of this year.
A2将是我们开始使用这个新版本电子表格的第一个日期的单元格，也是A454我费心将电子表格扩展到的最后一个日期，对应于今年年底。
Sub ShowAll()
Dim cell As Range
For Each cell In Range("A2:A454")
cell.EntireRow.Hidden = False
Next
End Sub
Sub RevealPast()
Dim cell As Range
For Each cell In Range("A2:A454")
If cell.Value < Date Then
cell.EntireRow.Hidden = False
End If
Next
End Sub
Sub HideFuture()
Dim cell As Range
For Each cell In Range("A2:A454")
If cell.Value >= Date Then
cell.EntireRow.Hidden = True
End If
Next
End Sub
回答by John Bustos
Try the cDatefunction.
试试这个cDate功能。
something along the lines of:
类似的东西：
If CDate("02/01/2013") > Date (or Now() if you want today's date) Then
...
So, in your example:
所以，在你的例子中：
If cDate(cell.Value) >= Date Then
I hope I understand your question correctly and hope this helps...
我希望我正确理解您的问题，并希望这有助于...
回答by S aziagba
I know this is a bit different from what you asked but this might help someone someday. If there is an hour added to the date (3/5/2014 8:00:00 AM) and you would like to compare with your date you can:
我知道这与你问的有点不同，但这可能有一天会对某人有所帮助。如果在日期上添加了一个小时（2014 年 3 月 5 日上午 8:00:00）并且您想与您的日期进行比较，您可以：
'X being the date you want to compare
'X 是您要比较的日期
x = CDate(x) 'formatting the date using the CDate function
x= Format(x, "MM/DD/YYYY") 'formatting the date by dropping the hour
x= CDate(x) 'formatting the date again
If x <= Date Then
...
