Compare two dates and times in VBA

th081

Board Regular
Joined
Mar 26, 2006
Messages
57
Hi All,

In excel i am importing two columns into Arrays and then comparing the datetimes that were in each column

I need to add one hour to one of the dates so i did the following:

dm = (CDate((DeliveryStart(p, 1))) + DateAdd("h", 1, de)) (de is just something i added into DateAdd it does not represent anything)

I then compare dm with another date like so:

Do Until (DateValue(dm) = DateValue(ContractStartDateandTime(j, 1)))

but the above is telling me that 02/05/2020 07:00:00 is the same as 02/05/2020 03:00:00

can you help in a better way of adding an hour and then comparing. The above i have cobbled from internet searches

Regards
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
700
Office Version
  1. 365
Platform
  1. Windows
I would give you a different solution from scratch. To do that I need to see how your arrays are declared and populated.

In general suppose you have two date/time values DT1 and DT2. You want to add 1 hour to DT2 and compare. The first problem is that date/time values are stored as real numbers, and due to the way computers work (I am not going to give you the long-winded explanation here), if you calculate the same number two different ways, the computer may not think they are equal. You should compare within a tolerance, rather than compare for an exact match.

VBA Code:
DT2 = DT2 + 1/24 ' Add 1 hour to the second date/time value

If DT2 > DT1 - 1/1440 And DT2 < DT1 + 1/1440 Then
    ' If we get here the two times are within 1 minute of each other
 

th081

Board Regular
Joined
Mar 26, 2006
Messages
57
Thank You 6StringJazzer for your help,

That works perfectly.

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,119,223
Messages
5,576,828
Members
412,748
Latest member
MikeyP14
Top