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?

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

Tyro



"Erika" wrote in message
...
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?

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.


--
HTH,
George


"Erika" wrote in message
...
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?

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

"Erika" wrote:

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?

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"
--
David Biddulph

"pdberger" wrote in message
...
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

"Erika" wrote:

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?

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" wrote in message ...
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?

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)" wrote:

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" wrote in message ...
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?

--

Dave Peterson

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

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

"Rick Rothstein (MVP - VB)" wrote:

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

LOL

Rick


"Dave Peterson" wrote in message ...
Well, I had to fix it a few times to get the quotes right <vbg.

"Rick Rothstein (MVP - VB)" wrote:

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!



"Rick Rothstein (MVP - VB)" wrote:

LOL

Rick

"Dave Peterson" wrote in message ...
Well, I had to fix it a few times to get the quotes right <vbg.

"Rick Rothstein (MVP - VB)" wrote:

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

--

Dave Peterson

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

Same with my formulas!

ROTFLOL

Rick