Converting seconds to minutes 2

[westicle47]

I'm trying to convert mobile phone call times from seconds to minutes. E.g. I have a cell containing the value 169 - this being 2 minutes 49 seconds. How can I convert this to hh:mm:ss or even just mm:ss?

Thanks

 

[Loomah]

Hi
Here's a possible non VBA method you could use. This converts to h:mm:ss

=TIME(TRUNC(A1/3600),TRUNC(A1/60)-(TRUNC(A1/3600)*60),MOD(A1,60))

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[westicle47]

Thanks Loomah

I thought there probably would be a way to do it with a formula but I had no idea how.

Chris.

 

[Loomah]

Chris
Thanks for the pip!
Just in case you wanted a VBA method here's one

Sub species()
Dim t As Date
Dim h As Integer
Dim m As Integer
Dim s As Integer

h = Int([a1] / 3600)
m = Int([a1] / 60 - (h * 60))
s = Int([a1] - ((h * 3600) + m * 60))
MsgBox h & " hours, " & m & " minutes and " & s & " seconds"

t = CDate(h & ":" & m & ":" & s)
MsgBox t
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi westicle47,

A slightly shorter way ..

in A1: [purple]169[/purple]
in B1: [blue]=A1/86400[/blue]

Format B1 as [blue][m]:ss[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[westicle47]

To be honest I just put the formula in to the code as it was

z = 0
            For x = 1 To ItemCount
                z = z + 1
                Cells(6 + z, 5) = "=TIME(TRUNC(" & Cells(6 + z, 4) & "/3600),TRUNC(" & Cells(6 + z, 4) & "/60)-(TRUNC(" & Cells(6 + z, 4) & "/3600)*60),MOD(" & Cells(6 + z, 4) & ",60))"
                Cells(6 + z, 5).NumberFormat = "[h]:mm:ss"
            Next

But I now have another problem with converting values into times. The times are listed like this: 11.08 = 00:11:08, 2152.38 = 21:52:38

any ideas?

 

[TonyJollans]

Hi westicle47,

What original value (seconds) do you have for [purple]2152.38[/purple] and how do you want the result to appear? And what happens when you do it my way?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[westicle47]

Sorry Tony I didn't explain clearly. This is a new query but I included it in this thread because you guys obviously new your stuff on this subject.

My query about 'seconds' has been answered - both yours and Loomah's methods work fine.

I'm now trying to format the times the phone calls were made. On the text file they appear as 2152.38, this being the time 21:52:38 (and this is the format I need to convert them too i.e. hh:mm:ss). Another example is 11.08 - That is how it currently looks and I want to change it to 00:11:08.

 

[PHV]

Something like this ?
x = 11.08
t = Format(x, "0000.00")
Debug.Print TimeSerial(Left(t, 2), Mid(t, 3, 2), Right(t, 2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi westicle47,

Try ..

[blue][tt]=INT(A4/100)/24+MOD(INT(A4),100)/1440+MOD(A4,1)/864[/tt][/blue]

.. formatted as [blue]hh:mm:ss[/blue] (or however you want it to show).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[westicle47]

Thanks guys. Most helpfull - I would have been stuck on this for a long time on my own.

Chris.