Time functions

[HumingBean]

Hello, all,

The Time functions in my Excel 2000 no doubt can do what I want, but I
can't fugure out a solution.

Assume that in Cell A1 I have the number 12.25, which represents 12
minutes and 15 seconds, i.e., it is a decimal representation of 12
minutes and another .25 of a minute.

Is there a way to get Cell A2 to show the following (without the
quotes)?

"12:15"

That's "12 colon 15"

Thanks.

 

[Sandy Mann]

In an unused cell enter 1440 then copy it and Paste Special into A1 using
the Divide option of Paste Special then format A1 as "m:ss" (without the
quotes)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

=--TEXT(A1/1440,"mm:ss")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[HumingBean]

In an unused cell enter 1440 then copy it and Paste Special into A1 using
the Divide option of Paste Special then format A1 as "m:ss" (without the
quotes)

Sandy Mann, thanks for responding, but I'm not sure I understand. If I
Paste Special into cell A1, won't that change it? I don't want to
alter A1, I want to have a different representation of its contents in
a DIFFERENT cell, say, A2.

Also, what is 1440? Why not 1480? Needless to say, I want this to
work for any decimal number in A1 such as 4.1 (four minutes and six
seconds) or 78.5 (seventy-eight minutes and 30 seconds).

Thanks.

 

[Sandy Mann]

Bob has already given you an answer. The reason for the 1440 is that there
are 1440 seconds in a day. If you can have mothe than 60 minutes then
format the cell as "[m]:ss" again without the quotations to prevent the
minutes rolling over into hours.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[HumingBean]

Bob and Sandy,

Thanks to you two.

Bob, I deleted the two hyphens between the equals sign and TEXT.
Should I have?

Sandy, I'm pretty sure there are more than 1,440 seconds in a day, and
I never did understand your original answer, but your formatting tip
improved Bob's answer perfectly.

Between the two of you you managed to answer my question, so good for
this newsgroup.

 

[Sandy Mann]

It is now 12:45 AM where I so it has been a looooong day. What I ment to
say was there are 1440 *minutes* in a day. As you are formatting for
minutes and seconds you have to change the minutes into a fraction of a day
by dividing by 1440 and the seconds are already a fraction of the minutes so
they wll be converted as well.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

Bob and Sandy,

Thanks to you two.

Bob, I deleted the two hyphens between the equals sign and TEXT.
Should I have?

No, it is part of the solution.

Sandy, I'm pretty sure there are more than 1,440 seconds in a day, and
I never did understand your original answer, but your formatting tip
improved Bob's answer perfectly.

He meant minutes in a day.

 

[HumingBean]

Bob, I deleted the two hyphens between the equals sign and TEXT.

Should I have?

No, it is part of the solution.

Bob, when I add the two hyphens back in the result is a decimal number,
but when I delete them the result is just what I want. What do the
hyphens do?

Thanks.

 

[Bob Phillips]

They change it from text to a number. You could leave it as text and it
looks right, but you can't add them, add to them etc. You do also have to
format it as mm:ss so that it looks right as well. Actually, I made a slip,
it should be

=--TEXT(A1/1440,"hh:mm:ss")

not

=--TEXT(A1/1440,"mm:ss")

and then format as mm:ss

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)