Convert Seconds into Hours:Minutes:Seconds

[Tyndie]

Hi,

Is there an easy way of converting seconds from using a time into hours, minutes, seconds please?

 

[VoG]

Try

=A1/86400

and format the result cell as Time. Note that 86400 is the number of seconds in a day (24*60*60).

 

[Tyndie]

Thanks, how can I do that in vba code?

 

[Richard Schollar]

Do you wish to apply this to a range of values? ie you have seconds values in A1:A100 that you want converted? In which case record a macro of you performing a PasteSpecial>Operation: Divide on the cells with 86400.

 

[Guanjin Peter]

range("a1") = range("a1").value / 86400


VOG II: w0w u in singapore?

 

[VoG]

Something like this

Sub test()
Const SecinDay As Long = 86400
Dim iSec As Long, MyTime As Date
iSec = 30000
MyTime = iSec / SecinDay
MsgBox MyTime
End Sub

 

[Tyndie]

I am using a timer to calculate the time a macro executes and then the time it executes to be in the status bar, but it shows it in seconds, and the script can run for several hours.

 

[VoG]

VOG II: w0w u in singapore?

Yep - just for a week (starting today) on business (nothing to do with Excel!)

 

[VoG]

Try this

Sub test2()
Dim i As Long, t As Date
t = Now
For i = 1 To 100000
    Application.StatusBar = Format(Now - t, "hh:mm:ss")
Next i
Application.StatusBar = False
End Sub

 

[Tyndie]

works great thanks