MIN and SUM functions in Google Sheets for Time

I'm trying to use the MIN and SUM function but the answer always comes back with either 0, ERROR or !DIV/0.

The format I want to have for my times are for example: 0.03, 10.56, 1:13.00.

=MIN example: =SUM example: One final thing I would want is that the last digit can only be 0, 3 or 6. So for example 1.03 + 1.06 + 1.03 = 3.13 and not 3.12.

• Sorry, it is mm:ss.ms Jan 20 '19 at 21:06
• you getting 0, ERROR or !DIV/0 because you are trying to calculate text format with number format. its same like if you would try to sum ten+10 and expect 20 Jan 20 '19 at 22:31

• first, you need to do is to change your input by entering it like 0:0:10.13 which will output as 00:10.13 if you will follow steps from image and custom format your column: • then you can sum it up with formula =SUM(A8+B10+D7): • lastly, to cover the last digit change you can use simple IF() to convert it based on logic rules:
=IF(AND(VALUE(RIGHT(C13, 1))>0, VALUE(RIGHT(C13, 1))<3),  LEFT(C13,7)&0,
IF(AND(VALUE(RIGHT(C13, 1))>3, VALUE(RIGHT(C13, 1))<6),  LEFT(C13,7)&3,
IF(AND(VALUE(RIGHT(C13, 1))>6, VALUE(RIGHT(C13, 1))<10), LEFT(C13,7)&6, C13))) • I applied the new time format "Minute (01) : Second (01) . Milliseconds (01)" to column A. Wrote the times in A1 to A3. gave A4 a function of "=SUM(A1+A2+A3)" and I get: i.gyazo.com/5d3163cf893e1ff0b5e6e1cdc97c30a0.png Jan 20 '19 at 22:48
• If I change the ":" into a "." in both the cells and in the format it all works. lol Jan 20 '19 at 23:04
• Think I got it to work now. was a bit confused about your first sentence about typing one thing and it will display in another. Thank you so much <3 Jan 20 '19 at 23:07