How to calculate elapsed time in seconds in VBA?

vba datediff
phan picture phan · Feb 21, 2012 · Viewed 40.7k times · Source

I have 2 strings, strStartTime and strEndTime.

strStartTime = "12:32:54" strEndTime = "12:33:05"

I want to find out how many seconds elapsed between strStartTime and strEndTime so I did this:

Dim dtDuration as date
dtDuration = DateDiff("s", CDate(strStartTime), CDate(strEndTime))

The result I get is dtDuration = "#1/10/1900#" in the Locals watch window.

Why does this happen? How do I get dtDuration to equal 11 for the 11 seconds that elapsed between the start and end times?

Answer

Radek picture Radek · Feb 21, 2012

Just change variable type to Long:

Dim dtDuration as Long

VBA converts numerical results of DateDiff functions into variable with date type.

Related questions

How to get a DateDiff-Value in milliseconds in VBA (Excel)?

I need to calculate the difference between two timestamps in milliseconds. Unfortunately, the DateDiff-function of VBA does not offer this precision. Are there any workarounds?

Read More
How to find the difference between dates in VBA

I am trying to find out the difference between the system date and the date stored in the worksheet. If the difference between them is > 30 days, the result is true, else the result is false Dim result as boolean …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More