VBA - Displaying how long a sub took to run

Question

I am trying to use the following code to display how long a series of commands took to run. In this example case, I expected it to come back with "10" or something similar.

Instead it comes back with:

What is going on and how can I format this correctly?

Sub timeyWimey()

    Dim t1 As Date
    Dim t2 As Date
    Dim timeTaken As Date

        t1 = Now()
        Application.Wait (Now + TimeValue("0:00:10"))
        t2 = Now()

        timeTaken = t2 - t1

        MsgBox timeTaken

End Sub

Edit:

Final Code after some great Answers:

Sub timeyWimey()

    'Dim t1 As Double
    'Dim t2 As Double


        t1 = Now()
        Application.Wait (Now + TimeValue("0:00:10"))
        t2 = Now()

        timeTaken = t2 - t1

        MsgBox Format(timeTaken, "nn:ss.000")

End Sub

Results in:

BAM! Problem Solved! Thanks everyone for your help!

Answer 1

Date's are stored as a numeric value within the MS Access and MS Excel. So if in your immediate window (Ctrl+G) you type ?Cdbl(now()) you will get a number like this: 42195.5204050926.

The whole numbers depicts how many days have passed since 1899 December 30th, and the decimal shows how much of the current day has passed.

So in your code you are basically saying something like this:

timeTaken = 42195.5222337963  - 42195.5204050926

In this example I just checked Now() once and then again a few minutes later. So I ended up with 0.0018287037.

Now if I go to display that using a Date variable such as in your example, am basically saying what time was it at 0.0018287037 which is December 30th 1899, 12:02:38 AM.

You can visually see this by going back to your immediate window and typing ?cdate(0.0018287037) and you will get a result like: 12:02:38 AM. To take it one step further you can then type ?cdate(1.0018287037) and you will get a result saying: 1899-12-31 12:02:38 AM

So in your situation you can simply just change:

MsgBox timeTaken

To:

MsgBox Format(timeTaken, "nn:ss")

Note: I hadn't noticed in the screenshot it says "Excel" though this answer should still be valid.