vba 如何在vba中比较时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8252094/
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 compare time in vba
提问by Omran
I want to write a macro that compares two times that is available in cells A1 and B1
我想编写一个宏来比较单元格 A1 和 B1 中可用的两次
I tried to use the following code BUT it gave me "type dismatch"at date1 = TimeValue(Range("A1"))
我试着用下面的代码，但它给了我"type dismatch"在date1 = TimeValue(Range("A1"))
for example, the value at cell A1 like this 11/18/2011 10:11:36 PM
例如，像这样的单元格 A1 处的值 11/18/2011 10:11:36 PM
dim date1 as date
dim date2 as date
date1 = TimeValue(Range("A1"))
date1 = TimeValue(Range("B1"))
if date1 > date2 then
'do something
else
'do something else
end if
采纳答案by smitec
you need to use Range("A1").Value
你需要使用 Range("A1").Value
回答by barrowc
Two things:
两件事情：
- try changing the value in A1 to
11/10/2011 10:11:36 PMIf things now work you may have a Regional Settings mismatch - you've declared
date1anddate2but are assigning twice todate1and never assigning todate2
- 尝试将 A1 中的值更改为
11/10/2011 10:11:36 PM如果现在工作正常，您可能会遇到区域设置不匹配的情况 - 你已经声明
date1并且date2但是分配了两次date1并且从来没有分配给date2
回答by Andrew R
use application.worksheetfunction.mod( date value, 1 )
使用 application.worksheetfunction.mod( 日期值, 1 )
You ought to understand that date and time in excel is represented by a serial number, in which 1 equals to a day, and time is repredented by decimals or fractions.
大家应该明白，excel中的日期和时间是用序列号表示的，其中1代表一天，时间用小数或分数表示。
All systems base their date from day zero which us January 1, 1900 = 1, and January 2, 1900 = 2 and so on.
所有系统的日期都是从零日开始的，即 1900 年 1 月 1 日 = 1，1900 年 1 月 2 日 = 2，依此类推。
On the excel worksheet you cab retrieve the current date snd time using today(). On vba you use Now instead. Todays date, in "0" or "General" number formatting should show a number that starts with 42..., which represents the number of days since January 1, 1900.
在 Excel 工作表上，您可以使用 today() 检索当前日期和时间。在 vba 上，您使用 Now 代替。今天的日期，在“0”或“常规”数字格式中应该显示一个以 42... 开头的数字，它代表自 1900 年 1 月 1 日以来的天数。
Since there are 24 hours within a day, if you wish to refer to 1 hour or 1:00 AM the fraction or decimal in the serial number is equalent to 1/24. 7:00 PM = 19/24
由于一天有 24 小时，如果您想指代 1 小时或凌晨 1:00，则序列号中的小数或小数等于 1/24。晚上 7:00 = 19/24
mod() is a formula or function that will return the remainder of a division. Remember that time is represented by decimals, you do not need the actual whole numbers.
mod() 是一个公式或函数，它将返回除法的余数。请记住，时间用小数表示，您不需要实际的整数。
You can use the mod() formula in vba by using "application.worksheetfunction." before any.
您可以通过使用“application.worksheetfunction”在 vba 中使用 mod() 公式。在任何之前。
When you divide a date and time with 1 using mod() it will return the remainder which is the decimal portion of your date aka the time.
当您使用 mod() 将日期和时间除以 1 时，它将返回余数，即日期的小数部分，即时间。
Comparing datevalue("1:00 PM") will not equal CDate("May 8, 2015 1:00 PM")
比较 datevalue("1:00 PM") 将不等于 CDate("May 8, 2015 1:00 PM")
回答by user2004685
Can't it be done by simply using .Text instead of .Value?
不能通过简单地使用 .Text 而不是 .Value 来完成吗？
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
回答by niko
sub compare_dates()
dim date1 as date
dim date2 as date
dim str as string
str = activesheet.range("A1").value 'str = "11/18/2011 10:11:36 PM"
str = Left(str,Instr(str," ")-1) ' str = "11/18/2011"
date1 = str ' assign it to date1
str = activesheet.range("B1").value ' in the same way b1 cell value
str = left(str,instr(str," ")-1)
date2 = str
if date1 > date2 then
' do something
end if
end sub
