Number of hours between 2 dates

  • Thread starter Thread starter Mike Proffit
  • Start date Start date
M

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.

using:
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
spreadsheet?

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.

Jamie
 
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
62:03:11
 
Back
Top