Calculating Times

[brshfdtrainer]

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.

 

[leibale]

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]

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]

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

 

[IML]

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]

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]

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]

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