How do I organize data on Microsoft Excel using time for the cell format?

Question

I'm using Microsoft Excel to organize data for my class involving Mile Times. The class of about 30 ran the mile in september and we recorded the time. They ran the mile again in December and we made a column to see the difference in time. The problem is that I'm trying to make the cells comply with a "time" format (h:mm:ss) but the numbers turn out all funny. We measured the time in minutes and seconds and would perfer to use a colon instead of a decimal since the seconds are supposed to be out of 60 and not 100. When i try to figure the difference in times my formulas/cell refernces are all funny and i get the ######### across the cell

What I would like the result to be is the september time in one column (in minutes and seconds) column two with the december time (in minutes and seconds) and the third column to be the difference in time (minutes and seconds). What format should I use and how would i go about making the formulas?
Thank you for all your help!

Answer 1

Hello Gypsy
You are making one simple mistake - never try to use the time format for singular time figures. It is a common mistake.

Simply highlight all of the cells concerned
Right hand click within the selection and chose format cells
Chose custom
In the type bar (will be filled with the word general) type in 00.00
Click ok

Sadly you can not use a comma.

Now when you come to add the formula the to sums will add correctly - (don't forget to format the cell as above with 00.00)
Sorry or subtract, it is all the same basically you want CellX - Cell X

And there you have it:

If you wish me to email you an example please simply email me with your email address.

Remember: 00.00 stand for minutes and seconds

Answer 2

Select your data to format. Then right-click and choose format cells. The first tab is number. Choose custom to format your time. Scroll the list and find your format. Then you need to subtract the two. Do you want to show improvement? If so subtract column B from column A.