Convert seconds to time

[Tod]

Here is an easy one for somebody.

I have a list of seconds in duration, like this:

124
1256587
2354
60521
235325

Why they chose to do it this way, (?). I want to format
this to show in the typical h:mm:ss format.

How?

tod

 

[Chip Pearson]

Use a function like

=A1/60/60/24



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

time is stored as a fraction of a 24 hour day, so you need to divide the
number of seconds by the number of second in a day

60521/(60#*60#*24#)

? 60521/(60#*60#*24#)
0.700474537037037

? format(60521/(60#*60#*24#),"hh:mm:ss")
16:48:41

 

[Bernie Deitrick]

Tod,

Enter the number 86400 (the number of seconds in a day) into a blank cell.
Copy that cell, select your cells with the seconds data, and choose
Pastespecial divide. That will convert your seconds data into decimal days
data, which is what Excel wants. Then simply format the cells as times, and
you are done.

HTH,
Bernie
MS Excel MVP

 

[RB Smissaert]

For this purpose I made a function once.
After some minor alteration it may suit your needs.


Function TimeUnitsFromSeconds(ByVal lSeconds As Long) As Variant

'takes an integer number of seconds and gives a 1-D 0-based arrray
'with 4 elements.
'first element hours, second element minutes, third elements seconds
'fourth element will give the combined units as a string
'-------------------------------------------------------------------

Dim lSecs As Long
Dim lMinutes As Long
Dim lHours As Long
Dim strTime As String
Dim arr(0 To 3) As Variant

lHours = lSeconds \ 3600
lMinutes = (lSeconds - (lHours * 3600)) \ 60
lSecs = (lSeconds - (lHours * 3600)) - (lMinutes * 60)

arr(0) = lHours
arr(1) = lMinutes
arr(2) = lSecs

If arr(0) = 0 Then
If arr(1) = 0 Then
strTime = arr(2) & " seconds"
Else
If arr(2) = 0 Then
strTime = arr(1) & " minutes"
Else
strTime = arr(1) & " mins, " & arr(2) & " secs"
End If
End If
Else
If arr(1) = 0 Then
If arr(1) = 0 Then
strTime = arr(0) & " hours"
Else
strTime = arr(0) & " hrs, " & arr(2) & " secs"
End If
Else
If arr(2) = 0 Then
strTime = arr(0) & " hrs, " & arr(1) & " mins"
Else
strTime = arr(0) & " hrs, " & arr(1) & " mins, " & arr(2) &
" secs"
End If
End If
End If

arr(3) = strTime

TimeUnitsFromSeconds = arr

End Function


RBS

 

[GD]

;o)))
hello !
slightly more concise like this :
=A1*"0:0:1"
@+
http://www.excelabo.net