How to correctly convert seconds to hh:mm:ss in Excel

Question

I am trying to calculate the time it takes to complete an analysis in Excel. I'm using DateDiff to calculate the number of seconds it takes and then try to format it in hours, minutes, seconds.

My code below causes an overflow error on line:

dTime = dTime / (60 * 60 * 24)

Do you know what I am doing wrong?

Dim dTime As Long
Dim startTime, endTime As Date

startTime = Now() ' at the start of the analysis
endTime = Now() ' at the end of the analysis

dTime = DateDiff("s", startTime, endTime)
dTime = dTime / (60 * 60 * 24) 'convert seconds into days
StatusBox.Value = "Analysis completed in " & Format(dTime, "hh:mm:ss") & "."

Answer 1

It seems that you are over-compensating. No conversion is necessary. Simply subtract the start time from the end time (optionally stored as a double as mentioned by @MatthewD) and format the cell as hh:mm:ss (preferred) or as a string representing time with Format(dTime, "hh:mm:ss").

Dim dTime As Double
Dim startTime As Date, endTime As Date

startTime = Now() ' at the start of the analysis
'analysis here
endTime = Now() ' at the end of the analysis

dTime = endTime - startTime 
StatusBox.Value = "Analysis completed in " & Format(dTime, "hh:mm:ss") & "."

Your startTime declaration was incorrect if you wanted it to be a Date type var. The method you were using created it as a variant.

Answer 2

Try this

Sub seconds()
Dim dTime As Variant
Dim startTime, endTime As Date

startTime = #8/7/2015 10:43:32 PM# ' at the start of the analysis
endTime = Now() ' at the end of the analysis

dTime = DateDiff("s", startTime, endTime)
dTime = dTime / 86400
MsgBox "Analysis completed in " & Format(dTime, "hh:mm:ss") & "."

End Sub

Answer 3

Try this

Dim startTIME, endTIME, totalTIME As Double

startTIME = Timer

'Your code Here

endTIME = Timer
  totalTIME = Round(endTIME - startTIME, 2)
MsgBox "Process completed successfuly in " & Int(totalTIME / 60) & " minutes, " & totalTIME Mod 60 & " seconds.", vbInformation