I am trying to enter cell phone usage minutes:seconds in Excel 2003
therefore, the minutes can be 4 or 5 digits (i.e. 12345:03). I have used a
custom format of [m]:ss. I have found I must enter the information as
0:xxxxx:xx. It shows in the formula bar as a date and time but in the cell
itself as minutes:seconds (the "0" entered in the "hour" place never shows).
Here's the kicker. This only works have the time but there is no rhyme or
reason as to why it sometimes works and other times doesn't. I have asked
every "expert" in our IT department and even attended an advanced Excel class
to try to get an answer but no luck. Can anyone help!?
--
Diane
City of Lakeland
Lakeland, FL

1. format the column into which you enter data as text
this means that 12345:45 or 1234:23 will appear just as you enter them

2. in a cell in a "helper" column enter:
=RIGHT(A1,2)/(60*60*24)+LEFT(A1,LEN(A1)-3)/(60*24)
Format this cell as [M]:ss
Copy the cell down the column to see the same values, but this time as real
times.



--
Gary's Student
gsnu200705


"Diane" wrote:

I am trying to enter cell phone usage minutes:seconds in Excel 2003
therefore, the minutes can be 4 or 5 digits (i.e. 12345:03). I have used a
custom format of [m]:ss. I have found I must enter the information as
0:xxxxx:xx. It shows in the formula bar as a date and time but in the cell
itself as minutes:seconds (the "0" entered in the "hour" place never shows).
Here's the kicker. This only works have the time but there is no rhyme or
reason as to why it sometimes works and other times doesn't. I have asked
every "expert" in our IT department and even attended an advanced Excel class
to try to get an answer but no luck. Can anyone help!?
--
Diane
City of Lakeland
Lakeland, FL

What is a "helper" column?
--
Diane
City of Lakeland
Lakeland, FL


"Gary''s Student" wrote:

1. format the column into which you enter data as text
this means that 12345:45 or 1234:23 will appear just as you enter them

2. in a cell in a "helper" column enter:
=RIGHT(A1,2)/(60*60*24)+LEFT(A1,LEN(A1)-3)/(60*24)
Format this cell as [M]:ss
Copy the cell down the column to see the same values, but this time as real
times.



--
Gary's Student
gsnu200705


"Diane" wrote:

I am trying to enter cell phone usage minutes:seconds in Excel 2003
therefore, the minutes can be 4 or 5 digits (i.e. 12345:03). I have used a
custom format of [m]:ss. I have found I must enter the information as
0:xxxxx:xx. It shows in the formula bar as a date and time but in the cell
itself as minutes:seconds (the "0" entered in the "hour" place never shows).
Here's the kicker. This only works have the time but there is no rhyme or
reason as to why it sometimes works and other times doesn't. I have asked
every "expert" in our IT department and even attended an advanced Excel class
to try to get an answer but no luck. Can anyone help!?
--
Diane
City of Lakeland
Lakeland, FL

any spare, unused column into which you can enter a formula. Let's say your
actual data is in column A and column H is un-used.

1. put the formula in column H and copy down.
2. copy column H
3. paste/special/value back onto column A
4. paste/special/format back onto column A

This will leave column a looking the same, but having real time intervals
that can be added, etc.
--
Gary's Student
gsnu200705


"Diane" wrote:

What is a "helper" column?
--
Diane
City of Lakeland
Lakeland, FL


"Gary''s Student" wrote:

1. format the column into which you enter data as text
this means that 12345:45 or 1234:23 will appear just as you enter them

2. in a cell in a "helper" column enter:
=RIGHT(A1,2)/(60*60*24)+LEFT(A1,LEN(A1)-3)/(60*24)
Format this cell as [M]:ss
Copy the cell down the column to see the same values, but this time as real
times.



--
Gary's Student
gsnu200705


"Diane" wrote:

I am trying to enter cell phone usage minutes:seconds in Excel 2003
therefore, the minutes can be 4 or 5 digits (i.e. 12345:03). I have used a
custom format of [m]:ss. I have found I must enter the information as
0:xxxxx:xx. It shows in the formula bar as a date and time but in the cell
itself as minutes:seconds (the "0" entered in the "hour" place never shows).
Here's the kicker. This only works have the time but there is no rhyme or
reason as to why it sometimes works and other times doesn't. I have asked
every "expert" in our IT department and even attended an advanced Excel class
to try to get an answer but no luck. Can anyone help!?
--
Diane
City of Lakeland
Lakeland, FL