CODEWITHSUNDEEP
excelvba
Jason
Jason

Reputation: 506

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

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") & "."

Upvotes: 5

Views: 21444

Answers (4)

revell
revell

Reputation: 1

I know it has been a while but it's still a relevant issue. @Jason, When declaring variables you need to set the type for each individual variable as otherwise it will set it to Variant by default.

Sub CalculateDuration()
Dim startTime As Date, endTime As Date

startTime = now                         'start of the analysis

'insert your code here

endTime = now                           'end of the analysis

Debug.Print "Analysis completed in " & Format(endTime - startTime, "hh:mm:ss") & "."
End Sub

Alternatively you can also use DateDiff to calculate the difference between start and end date. Make sure to devide the result of DateDiff by 86400 (numbers of seconds in 24 hours).

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

The DateDiff is probably overkill here and you don't really need it. But if you prefer to use, below settings might be useful. Replace the "s" in (DateDiff("s", startTime, endTime) with below parameters

Parameter (Description)

  • yyyy (Year)
  • q (Quarter)
  • m (Month)
  • y (Day of Year)
  • d (Day)
  • w (Weekday)
  • ww (Week)
  • h (Hour)
  • n (Minute)
  • s (Second)

Hope that helps.

Upvotes: 0

Irfan LATIF
Irfan LATIF

Reputation: 1

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

Upvotes: 0

Nitish
Nitish

Reputation: 341

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

Upvotes: 3

user4039065
user4039065

Reputation:

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.

Upvotes: 8

Related Questions