Compare two dates and times in VBA

[th081]

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

 

[6StringJazzer]

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.

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]

Thank You 6StringJazzer for your help,

That works perfectly.

Regards