More Excel stuff

[Vince]

So I got some great info yesterday, I was wondering if you all could help me again today.

I store the times that my kids achieve in their lap runs in an excel spreadsheet, and I keep the times in the following format -- 1:40 -- which means one minute and forty seconds. Excel wants to read this as 1:40:00 AM, which I honestly don't care about, as long as it displays it as 1:40. The problem is that when I use certain functions (notably =SMALL()), instead of returning 1:40, it returns something like 0.076594. I realize this is probably a formatting error, but when I attempt to change the format of the original cell, it converts 1:40 to the same decimal number of 0.076594 (or whatever it is). I've tried changing it from anything from general number to simple text, and each time it converts the data. Any ideas?

[wade moore]

You just have to change the format. Go to Format->Cells. Go to time. You want the one that says shows (at least in mine) 37:30:55.

[FrogMan]

Hey Vince, sorry I forgot to get back to you on this.

Remember that if you enter your data as "1:40" in a cell, it will always see it as hours and minutes and the decimal number is shown as what percentage of a day it is. To have that same percentage be shown back in a xx:xx kind of format, you'd have to go wade's way, in format-cells-specialized format (or I think it's what it's called, my excel here is in French) and then put in h:mm in the field to the right. Yeah, excel still computes it as hours (or percentage of the day) but it's showing as something that looks like minutes and seconds.

If you were to do some computations with that figrue, like add the times up for some sort of cumulative competition, I'd suggest entering the results in the h:mm:ss format, or in your case, 0:1:40.

As for your other question in the email, re: two same results, I have not found a way to get the second one to show I'm afraid...

FM

[Vince]

Well, that's ok -- there isn't much of the duplicate time thing happening, so it's not that big a deal. I haven't had a chance to goof around with the formatting (I never thought to change the format of the destination cell...duh), but I'll let you know what happens when I do!