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

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
Joined
Sep 12, 2002
Messages
336
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
Joined
Feb 15, 2002
Messages
1,743
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.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743

ADVERTISEMENT

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
Joined
Sep 12, 2002
Messages
3
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
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Apr 30, 2002
Messages
1,579
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
 

Forum statistics

Threads
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.
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
Top