Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 with @tiscali.co.uk "HumingBean" wrote in message oups.com... 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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=--TEXT(A1/1440,"mm:ss")
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HumingBean" wrote in message oups.com... 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. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Oct 22, 4:45 pm, "Sandy Mann" wrote:
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. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 with @tiscali.co.uk "HumingBean" wrote in message ups.com... 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. |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() "HumingBean" wrote in message ups.com... 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. |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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) "HumingBean" wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Hot key for time? | Excel Discussion (Misc queries) | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
Time and motion chart | Charts and Charting in Excel | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
M/S Visual C++ Run Time Error when attempting to use functions | Excel Worksheet Functions |