CODEWITHSUNDEEP
vbaexceltimecompare
Omran
Omran

Reputation: 559

how to compare time in vba

I want to write a macro that compares two times that is available in cells A1 and B1

I tried to use the following code BUT it gave me "type dismatch" at date1 = TimeValue(Range("A1"))

for example, the value at cell A1 like this 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

Upvotes: 3

Views: 23769

Answers (5)

Andrew R
Andrew R

Reputation: 11

use application.worksheetfunction.mod( date value, 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.

All systems base their date from day zero which us January 1, 1900 = 1, and January 2, 1900 = 2 and so on.

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.

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

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.

You can use the mod() formula in vba by using "application.worksheetfunction." before any.

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.

Comparing datevalue("1:00 PM") will not equal CDate("May 8, 2015 1:00 PM")

Upvotes: 1

ninja.coder
ninja.coder

Reputation: 9648

Can't it be done by simply using .Text instead of .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

Upvotes: 0

barrowc
barrowc

Reputation: 10679

Two things:

  1. 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
  2. you've declared date1 and date2 but are assigning twice to date1 and never assigning to date2

Upvotes: 2

niko
niko

Reputation: 9393

 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

Upvotes: 0

smitec
smitec

Reputation: 3049

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

Upvotes: 2

Related Questions