VBA 中的日期/时间比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12131201/
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
Date / Time Comparison in VBA
提问by Mike Wittmer
Goal: The excel sheet contains a list of activities. The goal is to compare the Date/Time of two activities. I need to identify if there is any overlap between the two. Column B contains the start date and time of each activity and column C contains the end date and time of each activity.
目标：Excel 工作表包含活动列表。目标是比较两个活动的日期/时间。我需要确定两者之间是否有重叠。B 列包含每个活动的开始日期和时间，C 列包含每个活动的结束日期和时间。
ActivityOneStartTime = Range("B41").Value
ActivityOneEndTime = Range("C41").Value
ActivityTwoStartTime = Range("B32").Value
ActivityTwoEndTime = Range("C32").Value
If ActivityOneStartTime >= ActivityTwoStartTime Then
If ActivityOneStartTime < ActivityTwoEndTime Then
booScheduleConflict = True
End If
End If
If ActivityOneEndTime <= ActivityTwoEndTime Then
If ActivityOneEndTime > ActivityTwoStartTime Then
booScheduleConflict = True
End If
End If
The above, of course, doesn't work. So, after trying a few other unsuccessful approaches, I've started going through the paces of setting up Case statments comparing first years, then months, then days, then hours, then minutes, but I'm sure there is an easier way.
以上当然是行不通的。因此，在尝试了其他一些不成功的方法后，我开始逐步设置案例统计数据，比较前几年、几个月、几天、几小时、几分钟，但我相信有更简单的方法。
Thank you in advance for any assistance with this challenge.
在此先感谢您对这一挑战的任何帮助。
回答by RBarryYoung
Like this:
像这样：
ActivityOneStartTime = Range("B41").Value
ActivityOneEndTime = Range("C41").Value
ActivityTwoStartTime = Range("B32").Value
ActivityTwoEndTime = Range("C32").Value
If ActivityOneStartTime < ActivityTwoEndTime Then
If ActivityOneEndTime > ActivityTwoStartTime Then
booScheduleConflict = True
End If
End If
回答by chris neilsen
I think your issue is determining the logic to test for overlap.
我认为您的问题是确定测试重叠的逻辑。
Try this
尝试这个
booScheduleConflict = Not(ActivityOneEndTime <= ActivityTwoStartTime or _
ActivityOneStartTime >= ActivityTwoEndTime)
回答by user2004685
It can be done by using TimeValue Property. Here is the example:
它可以通过使用 TimeValue 属性来完成。这是示例：
Dim date1 As Date
Dim date2 As Date
Dim date3 As Date
date1 = TimeValue(Range("A1").Text)
date2 = TimeValue(Range("B1").Text)
date3 = TimeValue(Range("C1").Text)
If date3 >= date1 And date3 <= date2 Then
'Yes!
Else
'No!
End If
