Format - Minutes, Seconds, hundredths

[Wingra]

My children use Excel to record their swimming results. How can they enter the times in minutes, seconds and hundredths? Whenever they try to do so, Excel thinks it should be hours, minutes and seconds.

I have tried without success to use the custom number formats to solve the problem. The spreadsheet allows me to enter the data using a mm.ss.hh format, but it won’t accept that format in spreadsheet formulas that calculate the difference between certain race times and time standards, motivational times, etc.

Thanks for any help you can provide.

 

[barry houdini]

Pre- format the cells as mm:ss.00 then you can enter e.g. 45:02.99 to mean 45 minutes 2.99 seconds

 

[Wingra]

Formatting the cells to mm:ss.00 did not work. For example,the spreadsheet shows the number as 3:43.91 but the box below the task bar indicates 12:02:44 AM. The formula does not work and displays "#VALUE."

 

[Cindy Ellis]

Ignoring what the task bar shows, I was able to calculate time differences using the mm:ss.00 format described above, but only when I pre-formatted all of the cells involved. Can you give an example of the input data, the formula you're using (or trying to use), and the expected output?

 

[barry houdini]

Formatting the cells to mm:ss.00 did not work. For example,the spreadsheet shows the number as 3:43.91 but the box below the task bar indicates 12:02:44 AM. The formula does not work and displays "#VALUE."

As Cindy says, you can ignore what the formula bar says, this will always display in that way. The times shown will be recognised as you wanted and you should be able to calculate with them.

I know this probably isn't exactly what you want to do but see this example where one column of times can be subtracted from another and the sum of those differences is calculated

Book1
	A	B	C	D
1	start	end	difference
2	02:34.87	04:45.65	02:10.78
3	10:32.36	28:18.74	17:46.38
4	19:39.76	20:31.13	00:51.37
5	23:33.60	34:44.15	11:10.55
6	36:36.87	46:45.11	10:08.24
7	10:09.80	30:27.75	20:17.95
8
9		total	1:02:25.27
10
Sheet1

All cells are formatted as mm:ss.00 except for total cell which is formatted as [h]:mm:ss.00