elapsed time in Excel for runners log

[Arjay13]

Hi
I like to keep track of my lap times when I run for training purposes. I'd like to use Excel to do this. The problem I have is when I have a lap that was faster than the previous lap. When I subtract the larger lap time from the smaller lap time to get the difference, all I get is "####" where the negative time should be (the negative difference indictaes it was faster).
For example, I ran a 1:44 lap and then a 1:54 lap. Subtract the first from the second and Excel gives me :10, which indicates that my second lap was 10 seconds slower than the first. But if I ran the 1:54 first, then the 1:44 and subtract the first from the second, Excel gives me ####. It doesnt seem to be able to handle the negative elapsed time.
Anyone know how to do this? Thanks
Arjay13

[Strawbs]

Excel usually gives those symbols if the cell is too small to show the full figure. Extend\Drag the cell width to the right to see the numbers in their entirety.

[Arjay13]

Strawbs (my cats nickname!)
I expanded the cells to 50 wide and all I see are ###########.........
Arjay13

[Strawbs]

Yup, I'm one cool cat.

Right-click the "Total" cell and select "Format", under the "Number" tab make sure "Number" is selected as the format type, check the decimal places amount isn't too exorbitent and click OK, if you haven't already.

edit: you can also double-click the Width bar to have it set the width automatically, If the sum is a long one you may not have extented the cell enough ...doing this would find the length auto.

[Arjay13]

Strawbs
I changed the format for the cell and revealed a number, -0.0000486111, which supposedly represents negative 4 seconds (-00:04). I guess that Excel is using its "Date/Time" serial representation.?
Arjay13

[Strawbs]

Repeat the "format" for all cells with nombers inserted.

[Arjay13]

Strawbs
How do you convert Excel's serial representation of time into minutes:seconds?
Arjay13

[Strawbs]

"Format" the cell again but this time select "Custom", scroll down the list until you reach "hh:mm:ss" or "mm:ss" if you're a fast runner, select and click OK, I'm not certain it will do Minus ( - ) tho, that's something you'll have to test for.

[Arjay13]

Strawbs
Yes, I formatted the times with mm:ss. You can then simply subtract the cells and then multiply that difference (which is in serial representation) by 86400 (the number of seconds in a day) to get seconds - this works for negative differences as well. I then applied conditional formatting to highligt differences that were negative. Thanks for the help.
Arjay13

[Strawbs]

Glad you got it sorted!