Number of hours between 2 dates


Mike Proffit

In VBA, I need to return the numbers of hours between 2 date/time values

Dim Time1 as Date
Dim Time2 as Date

Time1 = "6/2/2006 4:04:00 PM"
Time2 = "6/5/2006 6:07:11 AM"

I want to return the number of hours, minutes, seconds between the
date/times i.e. 62:03:11.

var = Format((Time2 - Time1), "hh:mm:ss") returns only 14:03:11, ignoring
the date difference.

Thanks so much,
Mike P


Do you have to use VBA, or are you looking to return these results in a

If you're looking for the results in a spreadsheet, subtract one from
the other, and set the formating in the results cell to custom and
change it to [h]:mm:ss this allows the hours to keep ticking past 24.
Can't get it to work in VBA... I'll keep looking.

a1 = date1
a2 = date2
a3 = date2 - date1 (This is the one you need to format)

Now this assumes date2 will always be greater. Be sure to check for
that if you're running anything complex.


Tom Ogilvy

the popular solution appears to be to use the Worksheetfunction Text

var = Application.Text((Time2 - Time1), "[h]:mm:ss")

demo'd from the immediate window

Time1 = cdate("6/2/2006 4:04:00 PM")
Time2 = cdate("6/5/2006 6:07:11 AM")
var = Application.Text((Time2 - Time1), "[h]:mm:ss")
? var

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question