# 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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### leibale

##### Active Member
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

#### IML

##### MrExcel MVP
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.

#### brshfdtrainer

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

#### IML

##### MrExcel MVP

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

#### brshfdtrainer

##### New Member
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

#### IML

##### MrExcel MVP
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?

#### Corticus

##### Well-known Member
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
6
Views
245
Replies
8
Views
239
Replies
1
Views
110
Replies
1
Views
145
Replies
3
Views
124

1,148,277
Messages
5,745,816
Members
423,980
Latest member
zimza

### 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