1

Example input:

date1 = "2015-03-23 07:06:17.855000"
date2 = "2015-03-23 07:06:17.870000"

When I use 

ans = datediff("s", date1, date2)

It produces the error: type mismatch

How can I fix this?

Community
  • 1
  • 1
Aoon
  • 21
  • 3

2 Answers2

1

Doesn't look like you've got a successful answer yet, so here's a possibility.

Dim t() as string
Dim d1 as long
Dim d2 as long

date1 = "2015-03-23 07:06:17.855000"
date2 = "2015-03-23 07:06:17.870000"
t = split(date1, ".")   'use the "." to split off the miliseconds
d1 = clng(t(2))         'grab the milliseconds, convert it to long
t = split(date2, ".")   'use the "." to split off the miliseconds from the other date
d2 = clng(t(2))         'grab the milliseconds, convert it to long

msgbox "Difference in milliseconds: " & cstr(d2-d1)
FreeMan
  • 5,660
  • 1
  • 27
  • 53
0

Try to convert dates stored as a string to date data type by using CDate("string_date"):

Dim date1 As String, date2 As String, ans As Long

date1 = "2015-03-23 07:06:17.855000"
date2 = "2015-03-23 07:06:17.870000"
ans = datediff("s", CDate(Left(date1, Len(date1)-7)), CDate(Left(date2, Len(date2)-7)))
'returns 0, because both date and parts are the same!

The smallest unit of DateDiff function is a second.

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • I see it now. DateDiff function can return time difference in seconds, but the real difference is in miliseconds. `DateDiff` function can't compare it, because the smallest unit is a second. When you pass date parameter this way: `CDate(Left(date1, Len(date1)-7)` it will execute without errors. – Maciej Los Apr 01 '15 at 09:45
  • @Maciej Los ans may have to be long, and your brackets are out of count, 7-"(" and only 6-")" – Davesexcel Apr 01 '15 at 10:38
  • 1
    [How to get a DateDiff-Value in milliseconds in VBA (Excel)?](http://stackoverflow.com/questions/939230/how-to-get-a-datediff-value-in-milliseconds-in-vba-excel) – Davesexcel Apr 01 '15 at 10:48
  • @Davesexcel, Interesting question and answers, but - as i mentioned - it's impossible to get miliseconds via DateDiff function. – Maciej Los Apr 01 '15 at 12:05
  • @Maciej Los-I didn't ask a question, I supplied a link to the Milli-seconds question and explained to you that your code does not have enough closing brackets and that "ans" should be long – Davesexcel Apr 01 '15 at 12:31
  • @Davesexcel, i know that you aren't a questioner. Answer has been updated. – Maciej Los Apr 01 '15 at 12:43