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

Mike Proffit wrote:
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

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


"Mike Proffit" wrote in message
...
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

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

MikeP



"Tom Ogilvy" wrote in message
...
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


"Mike Proffit" wrote in message
...
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