how to compare time in vba

Question

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 

Solution 1

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

Solution 2

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

Solution 3

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