Convert Seconds to Minutes

[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?

 

[Tyro]

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

Tyro

 

[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.

 

[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

 

[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"

 

[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

 

[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"

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

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?

 

[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

 

[Dave Peterson]

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

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

 

[Rick Rothstein \(MVP - VB\)]

LOL

Rick

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

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

 

[Dave Peterson]

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

Same with my formulas!

LOL

Rick

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

Same with my formulas!

ROTFLOL

Rick