counting time

[mmsed234]

I currently have a spreadsheet that tracks length of phone calls by minutes and seconds. I need to be able to total them and be able to calculate and average call time. Currently, 1 minute 30 seconds is seens as 1:30. I would like to convert that to 1.5. Any ideas on how to do this? Or is there a way to use that format to calculate formulas like Sum and Average?

 

[scottylad2]

Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:103.2px;"><col style="width:93.6px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>F</td><td>G</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:right; ">01:30</td><td style="text-align:right; ">1.5</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>G17</td><td>=F17*24</td></tr></tbody></table></td></tr></tbody></table>
Format G17 as general
Excel tables to the web >> Excel Jeanie HTML 4

 

[T. Valko]

I currently have a spreadsheet that tracks length of phone calls by minutes and seconds. I need to be able to total them and be able to calculate and average call time. Currently, 1 minute 30 seconds is seens as 1:30. I would like to convert that to 1.5. Any ideas on how to do this? Or is there a way to use that format to calculate formulas like Sum and Average?

You can use those functions on the time as is.

Just format the formula cell as [m]:ss.

The [ ] brackets will keep the minutes from rolling over into hours.

 

[scottylad2]

on second thought, it's the minutes your converting. The last will convert hours, this will do minutes again format as general

Excel Workbook
	F	G
17	00:01:30	1.5
Sheet1

 

[mmsed234]

Ok my main problem now is that i currently have in a cell 9:32 but when i click on the cell it shows it as 12:09:32 AM. How do I fix this problem?

 

[scottylad2]

format as general

 

[T. Valko]

Ok my main problem now is that i currently have in a cell 9:32 but when i click on the cell it shows it as 12:09:32 AM. How do I fix this problem?

That is not a problem.

That's just how Excel will display the time value in the formula bar.

 

[mmsed234]

Ok, so when i format as general I get a decimal that in not reflective of what i am looking for. Sorry, I am not good with Excel. It is still looking at the numbers as if they were a time. 9:32 looks like 12:09:32 AM. When I format that to general it becomes 0.006620373037037 <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=64 align=right></TD></TR></TBODY></TABLE>

 

[T. Valko]

Ok, so when i format as general I get a decimal that in not reflective of what i am looking for. Sorry, I am not good with Excel. It is still looking at the numbers as if they were a time. 9:32 looks like 12:09:32 AM. When I format that to general it becomes 0.006620373037037 <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right width=64 height=19></TD></TR></TBODY></TABLE>

Where do you see 12:09:32 AM, in the cell or in the formula bar?

If it's in the formula bar then that is simply how Excel displays the time value IN THE FORMULA BAR.

 

[mmsed234]

It's in the formula bar. I need excel to display it as 9:32 though. Not 12:09:32 AM. It is simply supposed to be 9 minutes 32 seconds.