How to get a Date Difference Value in milliseconds in VBA

[SeaAnne]

Hei :),

I've one table with dates in this format dd:mm:yyyy hh:mm:ss and in another column the milliseconds.

Do you have any suggestion how can I make the time difference in milliseconds?

I've done in this way..but ins't working for some cases


If Ms2 > Ms1 Then
Dif = DateDiff("s", Time1, time2)
Difms = ms2 - ms1
Dif= (Dif * 1000) + Difms
Else
Dif = DateDiff("s", Time1, time2)
Difms = (ms2 - ms1) + 1000
Dif = ((Dif - 1) * 1000) + Difms


Thank in advance for your help!

[SamT]

X = Abs((Time1 - Time2)*24*60*60*1000)

[SeaAnne]

X = Abs((Time1 - Time2)*24*60*60*1000)

Hi :)
Thank you for your help!

My time1 and time2 are in this format dd:mm:yyyy hh:mm:ss and I've in other column the milliseconds.
I've tried to do a new variable:
Timems1= time1 & "." & miliseconds
Timems2= time1 & "." & miliseconds

and I've write your formula


X = Abs((Timems1 - Timems2)*24*60*60*1000)

But appaear this error "Run-time error "13": Type mismatch" .any idea about what is wrong?

thank you! :)

[SamT]

and I've write your formula


X = Abs((Timems1 - Timems2)*24*60*60*1000)

that is the problem. I wrote the formula the way it should be.


My time1 and time2 are in this format dd:mm:yyyy hh:mm:ss
The Values in Time1 and Time2 are TimeSerial type. To see this, format the Cells as Number with 11 decimal places.

Excel store stores dates and times internally as a decimal number (a "Double" in programming language) and only displays them as Formatted. The Whole part of the number is the number of days since a given date in the past. The decimal part of the number is the decimal fraction of a day. ie: 0.25 = 1/4 Day or 6 hours.

[HiTechCoach]

cross postd:

http://www.access-programmers.co.uk/forums/showthread.php?p=1458276#post1458276

and

http://www.mrexcel.com/forum/microsoft-access/898318-how-get-date-difference-value-milliseconds-visual-basic-applications.html

[SamT]

Thanks, HighTech.

This poster is banned for one year for cross posting and failing to reply to anyone on any forum, presumably, after receiving a solution.