# Calculating Times

#### brshfdtrainer

##### New Member
We have a spreadsheet with minutes and seconds. We need to get an average time for several months. When I format the cells as mm:ss, I get am, pm. When I format the cells for [mm]:ss, I get large numbers that I did not put in. For example, I get 1160:00 for 18:36. We need to get this straight so we can get Excel to calculate the average, minimum, and maximum.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
you have to devide all your entries by 60, because now they where entered as hour:minutes
do it by writing in one cell the number 60, copy it, and then paste special it with the devide action into all the cells
tell me later if it is ok

On 2002-09-13 06:16, brshfdtrainer wrote:
We have a spreadsheet with minutes and seconds. We need to get an average time for several months. When I format the cells as mm:ss, I get am, pm. When I format the cells for [mm]:ss, I get large numbers that I did not put in. For example, I get 1160:00 for 18:36. We need to get this straight so we can get Excel to calculate the average, minimum, and maximum.

Something seems amiss here. Formats should not affect the calucations
18:36 should dsplay as 1116:00 with a [m]:ss format (18*60+36). Perhaps you should format your answer as [h]:mm so it looks consistent with the rest of your data formats.

Thanks for the input. I did already try the [h]:mm format. That did not work either.

Interesting. Perhaps you could post a few examples of what you have showing in your cells, and then show the same information when you apply the "general" format to them.

Example

The spreadsheet I am trying to help someone with is a list of average response times for each month since January for the different fire trucks, and other vehicles at the different stations. Needless to say, these are in minutes and seconds. Then the person needs an average response time for the 8 months and the minimum response time during the eight months and the maximum response time.
Jan Feb Mar Apr Example: Fire Engine 6:33 6:43 6:30 6:29

On 2002-09-13 10:07, brshfdtrainer wrote:
The spreadsheet I am trying to help someone with is a list of average response times for each month since January for the different fire trucks, and other vehicles at the different stations. Needless to say, these are in minutes and seconds. Then the person needs an average response time for the 8 months and the minimum response time during the eight months and the maximum response time.
Jan Feb Mar Apr Example: Fire Engine 6:33 6:43 6:30 6:29

So if you change you the format of the 6:33 cell to general, you get .004549? If you get .27, that is hours and minutes, not the minutes and seconds you may think you have?

I noticed you have to put your data in the format:
00:01:20 for XL to recognize the time as 1 minute 20 seconds (in mm:ss format)
if you put 1:20 if will return 20:00.

HTH,
Corticus

Replies
2
Views
306
Replies
1
Views
318
Replies
2
Views
184
Replies
2
Views
90
Replies
4
Views
389

1,218,756
Messages
6,144,302
Members
450,536
Latest member
DavidRankin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back