如何比较vba中的时间

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"))

我尝试使用以下代码，但它在date1 = TimeValue（Range（“A1”））给了我“type dismatch”

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

5 个解决方案

#1

you need to use Range("A1").Value

你需要使用Range（“A1”）。值

#2

Two things:

两件事情：

try changing the value in A1 to 11/10/2011 10:11:36 PM If things now work you may have a Regional Settings mismatch
尝试将A1中的值更改为11/10/2011 10:11:36 PM如果现在有效，您可能会出现区域设置不匹配的情况
you've declared date1 and date2 but are assigning twice to date1 and never assigning to date2
你已经声明了date1和date2但是为date1分配了两次而从未分配给date2

#3

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.

所有系统的日期都是从第0天开始，即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点= 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")

比较日期值（“1:00 PM”）将不等于CDate（“2015年5月8日下午1:00”）

#4

 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

#5

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

#1