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.

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

Thanks so much,
Mike P

 

[jseven]

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

 

[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
62:03:11

 

[Mike Proffit]

Fabulous. I owe you lunch. Actually, I owe you about 20 lunches over the
years!

MikeP

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



--
Regards,
Tom Ogilvy

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