Reputation: 89
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
Upvotes: 2
Views: 196
Reputation: 50162
Format
returns a Variant/String
and you can't assign that to a Double
. The linked answer is incorrect.
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
Upvotes: 4