E

#### Erika

get 2136 seconds. How do I convert that to minutes and seconds?

E

get 2136 seconds. How do I convert that to minutes and seconds?

Ad

T

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

minutes * 60.

Tyro

minutes * 60.

Tyro

G

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

Result:

35 minutes and 36 seconds.

P

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

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

=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

Ad

D

=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:

get 2136 seconds. How do I convert that to minutes and seconds?

R

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.=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")

Rick

D

Rick Rothstein (MVP - VB) said: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.Maybe slightly less ugly:

=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")

Rick

R

Rick

Dave Peterson said:

Rick Rothstein (MVP - VB) said: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.Maybe slightly less ugly:

=TEXT(A1/86400,"[m] ""minutes and ""s"" seconds""")

Rick

D

Same with my formulas!

Rick Rothstein (MVP - VB) said:

Rick

Dave Peterson said:

Rick Rothstein (MVP - VB) said:

=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

Ad

R

ROTFLOLSame with my formulas!

Rick

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.