Convert Seconds to Minutes

E

Erika

I have a phone report that is displaying total time is seconds, for example I
get 2136 seconds. How do I convert that to minutes and seconds?

T

Tyro

The minutes are: =int(seconds/60). The remaining seconds are =seconds -
minutes * 60.

Tyro

G

George Nicholson

If 2136 is in A1, and the following is in B2;

=INT(A1/60) & " minutes and " & MOD(A1,60) & " seconds."

Result:
35 minutes and 36 seconds.

P

pdberger

Erika --

If Excel knows that the 2136 is a time measurement, you can just format the
cell to minutes and seconds. If not, then here's one formula that works:

A B
1 2136 =INT(A1/60)& " minutes and "&A1-(60*INT(A1/60))&" seconds"

HTH

D

David Biddulph

If it is a number of seconds and you want to convert it to Excel time so
that you can format as minutes and seconds, divide your 2136 by 24, then by
3600. At that stage you can format as [m]:ss or whatever your preference
is.

If you want to stick to the way suggested below, the formula can be
simplified from
=INT(A1/60)& " minutes and "&A1-(60*INT(A1/60))&" seconds" to
=INT(A1/60)& " minutes and "&MOD(A1,60)&" seconds"

R

Rick Rothstein \(MVP - VB\)

As an alternate to the two-stage calculation, there is "ugly" formula...

=TEXT(A1/86400,"[m] \m\i\n\u\t\e\s a\n\d s \s\e\c\o\n\d\s")

or using a function call instead of the division by 86400 (=24*60*60)...

=TEXT(TIME(0,0,A1),"[m] \m\i\n\u\t\e\s a\n\d s \s\e\c\o\n\d\s")

Although, we can shortened these formulas slightly by not "escaping" the pattern string's non-metacharacters...

=TEXT(A1/86400,"[m] \mi\nut\e\s a\n\d s \s\eco\n\d\s")

(similar for the second formula).

Rick

D

Dave Peterson

Maybe slightly less ugly:
=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")

Or even using a formula of:
=A1/86400
and a custom number format of:
[m] "minutes and "s" seconds"

Rick Rothstein (MVP - VB) said:
As an alternate to the two-stage calculation, there is "ugly" formula...

=TEXT(A1/86400,"[m] \m\i\n\u\t\e\s a\n\d s \s\e\c\o\n\d\s")

or using a function call instead of the division by 86400 (=24*60*60)...

=TEXT(TIME(0,0,A1),"[m] \m\i\n\u\t\e\s a\n\d s \s\e\c\o\n\d\s")

Although, we can shortened these formulas slightly by not "escaping" the pattern string's non-metacharacters...

=TEXT(A1/86400,"[m] \mi\nut\e\s a\n\d s \s\eco\n\d\s")

(similar for the second formula).

Rick

Erika said:
I have a phone report that is displaying total time is seconds, for example I
get 2136 seconds. How do I convert that to minutes and seconds?

R

Rick Rothstein \(MVP - VB\)

Maybe slightly less ugly:
=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")
Thanks for noting that. I don't know why, but I keep forgetting that you can force the quote marks around pieces of text in order to escape the characters contained within them... some kind of mental block, I guess.

Rick

D

Dave Peterson

Well, I had to fix it a few times to get the quotes right <vbg>.

Rick Rothstein (MVP - VB) said:
Maybe slightly less ugly:
=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")
Thanks for noting that. I don't know why, but I keep forgetting that you can force the quote marks around pieces of text in order to escape the characters contained within them... some kind of mental block, I guess.

Rick

R

Rick Rothstein \(MVP - VB\)

LOL

Rick

Dave Peterson said:
Well, I had to fix it a few times to get the quotes right <vbg>.

Rick Rothstein (MVP - VB) said:
Maybe slightly less ugly:
=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")
Thanks for noting that. I don't know why, but I keep forgetting that you can force the quote marks around pieces of text in order to escape the characters contained within them... some kind of mental block, I guess.

Rick

D

Dave Peterson

If you like the law or sausage, you should never watch them being made.

Same with my formulas!

Rick Rothstein (MVP - VB) said:
LOL

Rick

Dave Peterson said:
Well, I had to fix it a few times to get the quotes right <vbg>.

Rick Rothstein (MVP - VB) said:
Maybe slightly less ugly:
=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")

Thanks for noting that. I don't know why, but I keep forgetting that you can force the quote marks around pieces of text in order to escape the characters contained within them... some kind of mental block, I guess.

Rick

R

Rick Rothstein \(MVP - VB\)

If you like the law or sausage, you should never watch them being made.
Same with my formulas!
ROTFLOL

Rick