The W
Views: 101457759
Main | FAQ | Search: Y! / G | Calendar | Color chart | Log in for more!
19.12.14 2310
The W - Random - Time in Excel
This thread has 201 referrals leading to it
Register and log in to post!
Thread rated: 7.36
Pages: 1
(1901 newer) Next thread | Previous thread
User
Post (8 total)
The Sham
Kolbasz








Since: 20.1.02
From: Hamden, CT

Since last post: 1783 days
Last activity: 1135 days
#1 Posted on | Instant Rating: 7.05
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).

http://www.ititches.com/rumble2002-4.xls

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.)

Thanks!



"Cram it with walnuts, ugly!"- Mr. Homer Simpson

"Nothing says 'bad ass' like beating up Randy Orton."- Matt Hocking, RAW SATIRE- January 21, 2003
Promote this thread!
Zeruel
Thirty Millionth Hit
Moderator








Since: 2.1.02
From: The Silver Spring in the Land of Mary.

Since last post: 1 day
Last activity: 6 hours
#2 Posted on | Instant Rating: 4.05
I think I can get what you want in Excel 97...

http://www.CatastrophicAnnihilation.com/ timetest.xls

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




"If you need to call me, my cell is 202-456-1414"

The Sham
Kolbasz








Since: 20.1.02
From: Hamden, CT

Since last post: 1783 days
Last activity: 1135 days
#3 Posted on | Instant Rating: 7.05
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!)



"Cram it with walnuts, ugly!"- Mr. Homer Simpson

"Nothing says 'bad ass' like beating up Randy Orton."- Matt Hocking, RAW SATIRE- January 21, 2003
Zeruel
Thirty Millionth Hit
Moderator








Since: 2.1.02
From: The Silver Spring in the Land of Mary.

Since last post: 1 day
Last activity: 6 hours
#4 Posted on | Instant Rating: 4.05
from the help file:

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

(edited by rikidozan on 24.1.04 1831)



"If you need to call me, my cell is 202-456-1414"

emma
Cherries > Peaches








Since: 1.8.02
From: Phoenix-ish

Since last post: 19 days
Last activity: 1 day
#5 Posted on | Instant Rating: 8.47
(edit: Oops. Just occurred to me why my thought doesn't hold up.)

(edited by emma on 24.1.04 1713)
The Sham
Kolbasz








Since: 20.1.02
From: Hamden, CT

Since last post: 1783 days
Last activity: 1135 days
#6 Posted on | Instant Rating: 7.05
I've got it figured out now.

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.)



"Cram it with walnuts, ugly!"- Mr. Homer Simpson

"Nothing says 'bad ass' like beating up Randy Orton."- Matt Hocking, RAW SATIRE- January 21, 2003
emma
Cherries > Peaches








Since: 1.8.02
From: Phoenix-ish

Since last post: 19 days
Last activity: 1 day
#7 Posted on | Instant Rating: 8.47
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.
The Sham
Kolbasz








Since: 20.1.02
From: Hamden, CT

Since last post: 1783 days
Last activity: 1135 days
#8 Posted on | Instant Rating: 7.05
    Originally posted by emma
    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.)


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!



"Cram it with walnuts, ugly!"- Mr. Homer Simpson

"Nothing says 'bad ass' like beating up Randy Orton."- Matt Hocking, RAW SATIRE- January 21, 2003
Thread rated: 7.36
Pages: 1
Thread ahead: Gatti/Branco
Next thread: Angel 1/21
Previous thread: Captain Kangaroo passes away
(1901 newer) Next thread | Previous thread
My parents live up the road from pieman in Montville, and they bought an old 1890 farmhouse about ten years ago for an insanely cheap price (even for a fixer-upper). While taking a drive through lovely Maine, it goes something like this:
- JayJayDean, Maine Real Estate? (2004)
The W - Random - Time in ExcelRegister and log in to post!

The W™ message board

ZimBoard
©2001-2014 Brothers Zim

This old hunk of junk rendered your page in 0.148 seconds.