Time Calculation Type Mismatch

Question

I am trying to calculate the seconds difference between two dates to two decimal places. However, I am getting an error 13, type mismatch whenever I execute the code. I have searched online and tried many variations, yet I am always ending up with a type mismatch. I'm not sure how to resolve this. Could someone please teach me how?

INFO: was originally using code from this question

Here is the current code:

Sub Query()
    Dim Beginning As Date: Beginning = Now: Dim Duration As Double
    ... more code ...
    Duration = Round((Now- Beginning) * 60 * 60 * 24, 2)
End Sub

This is working, thank you BigBen! Also, thank you Siddarth Rout.

By the way, I changed to the timer like you mentioned, BigBen, and it's much better. :)

Sub Query()
    Dim Beginning As Single: Dim Ending As Single
    Beginning = Timer()
    ... more code ...
    Ending = Timer()
    Duration = Format(WorksheetFunction.Round(Ending - Beginning, 2), "#0.00")
End Sub

Excellent service, thanks again!

Kind Regards, Joseph

Answer 1

1. Format returns a Variant/String and you can't assign that to a Double. The linked answer is incorrect.

2. The function currently is taking the result of DateDiff in seconds and converting it back to days by dividing. You'll need a different approach if you want fractions of a second; DateDiff can only return a difference in whole seconds.

Just take the difference between the two dates and multiply by 60 * 60 * 24. You can then round as necessary:

Public Function DifferenceInSeconds(ByVal EarlierDate As Date, ByVal LaterDate As Date) As Double
    DifferenceInSeconds = (LaterDate - EarlierDate) * 60 * 60 * 24
End Function