How to add CellPhone minutes from a bill to a sheet like 603:57?

T

TPS_Reports

I am trying to put together a sheet for my small company for cell phone cost
analysis. My phone bill displays time used in this format Min:Sec
I would like to know how I can format a cell to understand this. Also,
maybe someone might be doing something similiar to this and could suggest
some useful formulas.
 
D

Dominic

Peter,

Format your cells as custom - h:mm:ss under the Number tab in Format Cells.
Enter the data as 0:min:sec
You should be able to add up the lengths in this way.

Is that what you are looking for?
 
P

Pete_UK

If you have a lot of times to enter, it can be very tedious to have to
type the colons if you use the numeric keypad. Instead, I enter min:sec
as min.sec and then convert it into min:sec - I'll let you have the
formulae if this is of interest to you.

Other things that you might consider is to work out the rate charged
for each call type and compare this with published tariffs.

Hope this helps.

Pete
 
T

TPS_Reports

Yes this interests me. It would be nice to not have to use the : to complete
the cell. If your formula can let me enter the data with the min.sec and
then it changes it to a min:sec. That would be great.
 
P

Pete_UK

Okay, assuming you enter your data into column C, starting at C2, then
put this formula in D2:

=VALUE("0:"&INT(C2)&":"&MOD(C2,1)*100)

and format the cell using custom set to [m]:ss. You can then copy this
formula down several rows.

Now if you have a time like 10:33, you enter 10.33 in column C and this
will be converted to time format in column D. Once you have entered the
data, you can fix the values in column D using paste special and
values, and then delete column C.

Hope this helps.

Pete
 
Top