How to display time calculations in: "hh:mm:ss" format

[RunTime91]

Hello All...


I can make the following code provide the number of seconds between two times but I can't seem to translate those seconds into a "hh:mm:ss" format.

What am I doing wrong?

Dim Time1 As Date
Dim Time2 As Date
Dim TimeDiff As Double

Time1 = Me.txt22.Value
Time2 = Me.txt23.Value

TimeDiff = (Time2 - Time1) *86400

Me.lbl1 = Format(TimeDiff, "hh:mm:ss")

TimeDiff calculates the correct # of seconds but when I attempt to format those seconds into the "hh:mm:ss" format it shows 00:00:00

No amount of google or youtube reveals an answer, so..... here I am

Thank You in Advance...

RT91

 

[pjmorris]

Further to the post above:

PS. Having played further with the VBA Format statement I realise that it has some anomalies, which also explain Alex's comments further:
1. It doesn't handle durations in excess of 24hrs (using [hh].
2. Format statements such as "mm:ss" return a strange number. It needs "h:mm:ss" or "hh:mm:ss".
3. It doesn't handle negative times, even if the 1904 option is selected.

Hope this helps.

 

[RunTime91]

Greetings All and Thank You to those who have chimed-in on this thread.
Here is the latest which is sure to beg more questions than provide answers.

To those who are saying 'Remove the *86400' The solution (thus far) seems to indicate this is correct advice.
Problem is, when I remove it, the code returns 00:00:00 as an answer.

This code returns the answer mentioned above (00:00:00)

Dim Time1 As Date
Dim Time2 As Date
Dim TimeDiff As Integer '--> This was previously 'Double'

Time1 = Me.txt22.Value
Time2 = Me.txt23.Value
TimeDiff = (Time2 - Time1)
Me.lbl1 = Format(TimeDiff, "hh:mm:ss")

While this code consistently returns the correct answer.

Dim Time1 As Date
Dim Time2 As Date
Dim TimeDiff As Integer

Time1 = Me.txt22.Value
Time2 = Me.txt23.Value
TimeDiff = (Time2 - Time1) * 86400
Me.lbl1 = Format(TimeDiff / 86400, "hh:mm:ss")

Referring to Micron's BEDMAS comment, and as we all know, multiplication and division are inverse operations.
So why the first procedure returns 00:00:00 and the second one works perfectly is a mystery I would Love to know the answer too.

If anyone has idea's - I'm listening

Thanks Everyone

RT91

 

[MARK858]

Dim TimeDiff As Integer '--> This was previously 'Double'

Why did you change it to Integer?

 

[RunTime91]

Why did you change it to Integer?

Hey Mark...

I changed it to integer because as Double was giving me the answers in seconds down to the nth decimal

Thus 403 seconds would show as 402.82300000000004 seconds.

I hope I answered your question correctly...

 

[Micron]

I had to go back and see where I got the idea that you were not using parentheses in the equation but I can't find that. I thought you were doing b-a*86400 but you weren't Must have been something I messed up in the immediate widow. My apologies.

 

[Alex Blakenburg]

@RunTime91 There is no mention of having tried my suggestion in post #7.
Note: TimeDiff needs to be Double for that to work.

 

[RunTime91]

The VBA Format function doesn't support the duration format being [h]:mm:ss. Give this a try:

Dim Time1 As Date
Dim Time2 As Date
Dim TimeDiff As Double
Dim sfnTimeDiff As String

Time1 = Me.txt22.Value
Time2 = Me.txt23.Value

TimeDiff = (Time2 - Time1)
sfnTimeDiff = "=Text(" & TimeDiff & ",""[h]:mm:ss"")"

Me.lbl1 = Evaluate(sfnTimeDiff)

Hey Alex,

My apologies for the delayed response.

The code above... Works Perfectly!!

If I understand correctly, your code is processing the TimeDiff as a String
Then, by using the 'Evaluate' function, it turns that String back into a Time Value that can be calculated.

I had to go back and see where I got the idea that you were not using parentheses in the equation but I can't find that. I thought you were doing b-a*86400 but you weren't Must have been something I messed up in the immediate widow. My apologies.

No apology required my Friend...You are far too helpful to be apologizing...

When I have time I'm going to evaluate my code (the one that works) in the Immediate Window and see if I can understand why it works?? The Multiplying then Dividing has me shaking my head.