For the past two years I've put together an Excel spreadsheet to serve as sort of a box score for the Royal Rumble (and to determine the winner of our RR pool).

I have never been able to get a function to correctly determine the total time a wrestler spends in the ring, however. I always have to do it by hand. This is because Excel treats times as "time of day" instead of "number of minutes and seconds." Whenever I try to use the "time" format, it converts whatever I enter into a time of day and just generally pisses me off.

I've always had to enter times as text. Are there any Excel experts out there who can help me figure out how I can get this to work without having to calculate everything by hand or convert times to decimals? I mean, that is the point of Excel, right? I'm using Excel v.X for Mac, but I'm sure I could figure out how to make any advice you might have for your version work for me.

(BTW, you would think using the m:ss format would work, but it doesn't.)

I have the times in a hh:mm:ss format and my data is:

Time In Time out Total Time Hulk 0:00:00 0:03:00 0:03:00 Andre 0:02:00 0:39:00 0:37:00 Ax 0:04:00 0:39:00 0:35:00 Smash 0:06:00 0:39:00 0:33:00 winner!

Hulk went in at 0 mins Andre at 2 Ax at 4 Smash at 6

Hulk was out at 3 everyone else at 39 (Smash eliminated Andre and Ax)

Hulk's total time 3 mins Andre's time 37 Ax's time 35 Smash's time 33

Yeah, that does work. it's too bad it stores the data as a time of day, though. I guess I could enter everything as 0:00:00 and then change the format after the calculations are complete to drop the leading zero (which is really superflous). Thanks! (And if anyone knows of a good way to do this without entering every time as a time of day, please let me know!)

To display hours, minutes, and seconds, include the following format codes in a section.

To display Use this format code Hours as 0-23 h Hours as 00-23 hh Minutes as 0-59 m Minutes as 00-59 mm Seconds as 0-59 s Seconds as 00-59 ss Hours as 4 AM h AM/PM Time as 4:36 pm h:mm am/pm Time as 4:36:03 p h:mm:ss a/p Elapsed time in hours; for example, 25.02 [h]:mm Elapsed time in minutes; for example, 63:46 [mm]:ss Elapsed time in seconds [ss] Fractions of a second h:mm:ss.00

If the format contains an AM or PM, the hour is based on the 12-hour clock, where "AM," "am," "A," or "a" indicates times from midnight until noon, and "PM," "pm," "P," or "p" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock. The "m" or "mm" must appear immediately after the "h" or "hh" format code or immediately before the "ss" code, or Microsoft Excel displays the month instead of the minutes.

--------

So, I guess you want Elapsed time in minutes, so use the code [mm]:ss

I'll have to enter each time at 0:2:00 or 0:39:15 instead of just 2:00 or 39:15, but everything works out all right (and it displays the way I want it to display).

My only qualm at this point is that it stores the numbers I enter as a time of day (0:2:00 is 12:02 AM). The rumble numbers or race times (if you want a non-wrestling application) have nothing to do with the time of day, and I think it's odd that Excel doesn't have a way to handle time in this manner.

(I went with h:m:ss for the entry and elimination times and [m]:ss for the total times.)

And I've got figured out the kluge I was thinking of so you *don't* have to put the leading "0:" ...

Format all the salient cells as "[hh]:mm". If you enter something like "39:15", you & I know that it's 39 minutes & 15 seconds, but we'll let Excel think that it's 39 hours & 15 minutes. All the arithmetic works out the same. (60 min/hour or 60 sec/min.)

Yeah, the values kept internally are an "order of time magnitude" off, but you get the data entry & results that you're looking for.

Originally posted by emmaFormat all the salient cells as "[hh]:mm". If you enter something like "39:15", you & I know that it's 39 minutes & 15 seconds, but we'll let Excel think that it's 39 hours & 15 minutes. All the arithmetic works out the same. (60 min/hour or 60 sec/min.)

Thanks, emma, that works perfectly. I had tried that without the brackets, so I had been limited to 23:59, but now I see that the brackets will let me go as high as I need. (I went with [h]:mm so it doesn't force a zero infront of the early entrants.)

With little math to do, I should be able to get the Excel chart up in the Wrestling folder in just a few minutes after the Rumble ends (unless I have to give people rides home afterwards). Look for it in whatever Rumble discussion thread looks most relevant. I know you're on the edges of your seats!

You are Laguna! Although you're way too shy with the opposite sex, you've got a heart of pure gold. You're a good leader and well liked by those who know you. Most of your friends look to your good sense when they need advice.