Time functions

  • Thread starter Thread starter HumingBean
  • Start date Start date
H

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.
 
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
 
=--TEXT(A1/1440,"mm:ss")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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)
Click to expand...

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.
 
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
 
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.
 
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
 
HumingBean said:
Bob and Sandy,

Thanks to you two.

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


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.
Click to expand...


He meant minutes in a day.
 
Bob, I deleted the two hyphens between the equals sign and TEXT.
Should I have?
Click to expand...

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.
 
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)
 
Back
Top