Excel 2007 : Minutes and seconds

[Excelminator]

Hello forum!

I have a little problem...
I am doing the results for a racing competition.
The winners time is 6 minutes 50,10 seconds. How do i write this in so it appears like this: 6:50,10 ?
I also want to do a "gap to next driver" column. Like if the second placers time is 6:51,10 it should say +0,1. And I would of course do a formula for this but I don't know how yet as I don't know how to enter the times.

I hope you understand my poor english and my problem.

Thank you - Excelminator

[MarvinP]

Hi Excelminator,

Look at Format Cells Custom using mm:ss.0. You simply need to format your cells correctly.

You should be able to simply subtract these to get differences from the winner.

If that isn't it, then supply a sample and let us look.

[Excelminator]

How do I then enter the values using mm:ss.0?

If i put in two cells
A1 11:13.13
A2 11:12.13

and then in A3 =A1-A2 I got #VALUE!

[MarvinP]

Hi,

Your above example worked for me. Could you supply a short attached file with the error and show why it didn't work?

[BigDat6CC]

Hello

Input the times in the mm:ss.0 form and, as Marvin says, format the cells using the mm:ss.0 custom format. Then format cell A3 as s

This works for me with the values copied and pasted from your post #3 into Excel.

[Excelminator]

I still get VALUE

Could someone do a noob-step-by-step for me as I just cant get it right.

[MarvinP]

Hi Excelminator,

Start a new reply, click on "Go Advanced" and click ont he PaperClip Icon above the message area. This will allow you to attach an example file that shows your #Value. We can all remember being noobs and would love to help.

[Excelminator]

Here you go...

[MarvinP]

Hi,
I opened your workbook, changed cell formats to mm:ss.0 and then entered in each cell again. This entailed putting the curson at the end of the number and pressing enter. After doing this to each cell all was great.

I'm attaching to show you it looks ok on my machine. If you still show #Value, I'd check each value in A1 and A2 to insure they are numbers and not strings.

[MarvinP]

Here is the .xls as I see you are using 2003 Excel.

[Excelminator]

I'm sorry, I'm using 2007. I corrected my settings.

I will try It out.

EDIT: It worked now but i need 2 decimals... How do I get that?

[MarvinP]

Go to Format Cells.
Click on Custom and Type of mm:ss.0
Now just below the word Type: add a zero at then end to make it
mm:ss.00
Click OK and see you have two decimal numbers after your time values.

hope that helps.

[TonySom]

Solved my problem too!
Set up a soft boiled egg timer chart but needed 8.2 sec per 2gm increase in weight.
Been going round in the proverbials.
Forum yet again came to my rescue!
Start time for 50gm = 3min:56sec (egg into rolling boil water)
so =cellbeforetime + a static reference cell of 8.2secs. Cell formatting does the rest.
Note: the static cell content displays 00:00:08 but you can enter 8.2 into the cell, Excel quirk?