Thread: Need assistance with Excel 2010 (Format or Formula)

1. Member
Join Date
Oct 2008
Posts
43

Need assistance with Excel 2010 (Format or Formula)

I get a bill from my telephone company every month where it shows the length of the calls as follows:

Formula is 12:03:00 AM but the text in the cell displays 03:00.0
Formula is 1:00:00 AM but the text in the cell displays 00:00.0
Formula is 2:23:00 AM but the text in the cell is displayed as 23:00

The idea is that the first line is 3 minutes, the second line is 60 minutes and the third line is 143 minutes.

I need to be able to display these numbers in mm:ss. I can change the format to [mm]:ss to chenge the display as: 3:00, 60:00 and 143:00 respectively.

However, when I try to do a formula to determine the rate/min, it does not calculate properly.

Length of Amount
Call m:s
03:00.0 \$0.72
23:00.0 \$4.29
00:00.0 \$1.80

So my question is how do I get the rate/min from the above 2 cells. If I try a formula like "=0.72/12:03:00 AM", I get \$345.60 which is incorrect. The formula should reflect the "real" minutes as "=0.72/3" which is \$0.24/min.

I hope this makes sense to someone that can help.

Thanks.
Z.  Reply With Quote

2. Member
Join Date
Dec 2007
Posts
2,291

Re: Need assistance with Excel 2010 (Format or Formula)

You will just need to enter the values as 0:mm:ss and format as [m]:ss. Since this will be now in the minutes and seconds so the simple maths will let you to calculate your stats.  Reply With Quote

excel, microsoft office, ms office, office 2010 