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

 

[Micron]

There is a DateDiff function that should be easier than doing math. It also provides for formatting the calculation.

DateDiff Function - Microsoft Support

Use the DateDiff function in Access to returns a Variant (Long) specifying the number of time intervals between two specified dates.

support.microsoft.com

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

 

[RunTime91]

Hey Micron ~ Thanks for posting as always...

I played with DateDiff prior to landing on the code I posted. and I did look at those suggested articles you posted... Unfortunately, no help...(it seems those articles are MS Access centric)

Everything I have been able to find tells me my code is correct... and, as mentioned, I am getting the correct calculation in seconds, but my code simply will not produce a response in the "hh:mm:ss" format.

Sad Times

 

[AdriLand]

Hi there.

You can use a formula and formatting, which should be much easier.
Let us assume your start date and time is in A1, end date and time in B1 and the difference should go into C1, in your required format.
In C1, enter =B1-A1 (essentially end date minus start date)
Select column C and on the Home Tab go to Numbers and then More Number Formats.
Under category select custom
Then select [h]:mm:ss and your calculation will be in the correct format.

Perhaps, if you really want to use a macro, change the format to [h]:mm:ss rather than hh:mm:ss

Good luck.

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]

Hi, your error is in the calculation TimeDiff = (Time2 - Time1) *86400 since you don't need the time in seconds. For Format(TimeDiff, "hh:mm:ss") to work correctly the TimeDiff must be a time value which is a decimal value representing the proportion of a 24hr clock. Thus 12:00 would be 0.5.

Simply remove the *86400 from you calculation and it should work.

For info, days are integer values starting in 1 Jan 1900.

 

[Micron]

FWIW, DateDiff is a vba function and is not specific to Access or Excel. This link may have made that more evident.

DateDiff function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

TimeDiff calculates the correct # of seconds

To get a calculation formatted the way you want involves 3 calculations; 1 to get the days, 1 to get the hours, and 1 to get the remaining seconds IF you want numbers. Since you seem to want a string, this should work: format(Now()-#01/12/2025#,"hh:mm:ss") using your own values of course. Note that my date is enclosed in octothorpes (hash tags) so that the calculation is performed on two dates, not a date and a string, which I suspect is what you're trying to do. You could try with literal dates as I've done to see that it works. If using vba for this, you'll then have to concatenate the cell value with the tags, or pass the cell value to a variable that is type cast as a date variable. As always, sample data will often help you get more focused answers.

EDIT - after review I see that you have date variables so ignore that. Notice that as suggested, you should not need to multiply by 86400
EDIT 2 -. I can now see why you get 0. Remember BEDMAS?

 

[Alex Blakenburg]

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)

 

[Micron]

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

Not sure I understand that statement. Seems to work with hh or just h:
?format(now() - #01/15/2025 10:45:00#,"h:mm:ss")
15:53:34

 

[Alex Blakenburg]

Not sure I understand that statement. Seems to work with hh or just h:
?format(now() - #01/15/2025 10:45:00#,"h:mm:ss")
15:53:34

You need the duration format when you exceed 1 day (24 hrs), change your calculation to go over 24 hrs and see what you get.
PS: Also your current formula produces a negative which will often produce an error when using date formats. In your example it is ignoring the sign. My current proposal will produce an Error 2015 if the result is negative which may need to be handled.

 

[pjmorris]

The problem with the OPs original problem was simply the inclusion of *86400. If the code were rewritten as:

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)

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

To pick up on the other comments. The key point in Alex's post #7 is the inclusion of '[h]' in the formatting statement which presents the 'hours' as a total number, even if it goes over 24hrs. To manage negative time go to Options, Advanced and scroll down (quite a way) to 'When calculating this workbook' and tick the 'Use 1904 Date System'; doing so allows correct handling of negative time. The spreadsheet below demonstrates this. For info, being able to use negative time allows calculation of positional data based on Latitude and Longitude.

Hope this helps.

Book1
	A	B	C
1	Date Time as Double	Date Time Formatted as "dd mmm yy hh:mm:ss"	Difference formatted as "[hh]:mm:ss"
2	45671.49028	15 Jan 29 11:46:00
3	45671.49471	15 Jan 29 11:52:23	00:06:23
4	45669.4375	13 Jan 29 10:30:00	-49:22:23
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=A2
C3:C4	C3	=B3-B2