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.

 

[Alex Blakenburg]

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.

Note quite.
The goal is to populate Me.lbl1 which holds values as Text with the Time difference in hours minutes and seconds where if the time difference is multiple days that is reflected in the hours component.
1) Date & Time are stored in Excel as numbers with Time being the decimal part of the number.
Therefore the TimeDiff variable needs to be Double
(at one stage you used Integer which causes you to lose the time part and only keeps days)

2) The format hh:mm:ss and most variations can be referred to as Time formats and will only show up to 24 hours.
[h]:mm:ss is a Duration format (a term used in Power Query as well) and is not bound by the 24 hour cap.

3) If the "VBA Format function" supported the duration format we could go straight to
Me.lbl1 = Format(TimeDiff, "[h]:mm:ss")
Unfortunately it does not support it.

4) The worksheet function Text does support it but like many functions that have a VBA equivalent function (in this case Format), you can't access the WorksheetFunction.Text
By using Evaluate we can interpret a String (most commonly a formula) as if it was being used by the spreadsheet.
I have done it in 2 lines, firstly putting the string together and then wrapping that string in Evaluate to produce the result. You could have done it in one step.
The Text String should look exactly like it would if you used as a formula in the sheet.
ie in the immediate window using my sample dates
? sfnTimeDiff
=Text(232.510763888888,"[h]:mm:ss")
And the next step:
? Evaluate(sfnTimeDiff)
5580:15:30

Let me know if any of that is not clear.