Seconds to hh:mm:ss in Excel

[ECEK]

My colum has seconds in it.
I want to convert these seconds to hh:mm:ss so that I can then subsequently add and subtract them.

Examples:
157164 should show as 43:39:24

86401 should show as 24:00:01

The result of adding these times together should subsequently be 67:39:25

 

[pr2-eugin]

You can create a function something like..

Function SecToHourString(lSeconds As Long) As String
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim lHour As Long, lMin As Long, lSec As Integer
    
    lHour = lSeconds \ 3600
    lSeconds = lSeconds Mod 3600
    
    lMin = lSeconds \ 60
    
    lSec = lSeconds Mod 60
    
    SecToHourString = Format(lHour, "00") & ":" & Format(lMin, "00") & ":" & Format(lSec, "00")
End Function

 

[Brianwarnock]

My colum has seconds in it.
I want to convert these seconds to hh:mm:ss so that I can then subsequently add and subtract them.

Examples:
157164 should show as 43:39:24

86401 should show as 24:00:01

The result of adding these times together should subsequently be 67:39:25

Whilst you may want to convert the result it will be much simpler to do the arithmetic with the seconds.

Brian

 

[ECEK]

Thanks for the reply Brian
This whole scenario stems from having to add up lengths of time within Access.
If somebody works 07:00:00 hours a day then after 5 days they will have worked 35:00:00
Access doesn't do this !!!
I have converted everything to seconds added the seconds up then formatted it back to hh:mm:ss
The only problem Im now having is that I need to deal with negative figures in order to illustrate trends on a report eg.

Hours worked: 67:00:00
Target Hours: 80:00:00
Shortfall: -13:00:00

I then need to export it from Access into Excel and be able to play with the figures.
I just can't get it to play ball !!!!
It's driving me mad

 

[pr2-eugin]

Not sure if you got an opportunity to play with the function I provided, but as Brian suggested, do the arithmetic with seconds. So the final answer could be.

? SecToHourString(-46800)
-13:00:00

The function needs to be changed a bit though.

Function SecToHourString(lSeconds As Long) As String
    Dim lHour As Long, lMin As Long, lSec As Integer
    
    lHour = lSeconds \ 3600
    lSeconds = lSeconds Mod 3600
    
    lMin = Abs(lSeconds \ 60)
    
    lSec = Abs(lSeconds Mod 60)
    
    SecToHourString = Format(lHour, "00") & ":" & Format(lMin, "00") & ":" & Format(lSec, "00")
End Function

 

[ECEK]

Hi Eugin
Many thanks for you post. Im afraid I have no idea how to apply the functions etc.
If you would be so kind as to illustrate?

I will have a column of seconds. Column A.
I want to get the formatted results in Coulmn B.

 

[NBVC]

Using formulas....

Assuming your first time is in A1 and your second is in B1 and the sum in C1

You can convert those times in A2 and B2 with formula in A2:

=A1/(60*60*24) or =A1/86400

copied across. Then format these cells as Number: Custom with Type: [hh]:mm:ss

You can also just get the sum on its own with:

=(A1+B1)/86400

 

[ECEK]

NBVC

This has solved everything.
Brilliant
Many thanks