Calculating Times

brshfdtrainer

New Member
Joined
Sep 12, 2002
Messages
3
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top