|
05-24-2014, 03:25 PM
|
#1
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,887
|
Time in Excel
I have race time in Excel that show up as time of day.
A Belmont stakes time shows up a 02:30.1, but throws in an AM.
If I find and replace the : with a . the whole value changes.
Can't find anything on google that does what I want - convert it to a time, not a time of day.
Anyone doing this?
__________________
Who does the Racing Form Detective like in this one?
|
|
|
05-24-2014, 04:08 PM
|
#2
|
Registered User
Join Date: Oct 2003
Location: Columbus, Ohio
Posts: 396
|
Hopefully someone will know the absolute best solution but I've converted the time to text in the past.
|
|
|
05-24-2014, 04:20 PM
|
#3
|
Veteran
Join Date: Mar 2009
Posts: 9,893
|
Why wouldn't you just convert the time to seconds (e.g., 2:04 = 124)?
|
|
|
05-24-2014, 05:12 PM
|
#4
|
Registered User
Join Date: Aug 2013
Posts: 1,751
|
I am a bit away fom my computer right now, but you should be able to set a format that fixes this.
If they don't have one, I think you would wan to set up a custom one in the format mm:ss.0
Hope this helps. If not, I will check in late tonight.
|
|
|
05-24-2014, 05:53 PM
|
#5
|
Registered User
Join Date: Dec 2011
Location: route 66
Posts: 1,112
|
Try entering it with ' before it: '02:30:1.
That should work.
If it doesn't copy and paste the time format that you want from another cell. And enter the new value (sometimes a cell seems to get stuck in a format).
Last edited by Dark Horse; 05-24-2014 at 05:55 PM.
|
|
|
05-24-2014, 05:57 PM
|
#6
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,887
|
I'm not entering it - I am getting the times from Wikipedia, and they come in the time of day format. I want to change them to a format I can use, a decimal number.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
05-24-2014, 06:01 PM
|
#7
|
Registered User
Join Date: Dec 2011
Location: route 66
Posts: 1,112
|
Quote:
Originally Posted by Tom
I'm not entering it - I am getting the times from Wikipedia, and they come in the time of day format. I want to change them to a format I can use, a decimal number.
|
You may have to set up a separate sheet to transfer those imported values into the ones you want.
Can you give an example of what you import and what you want to end up with?
|
|
|
05-24-2014, 06:26 PM
|
#8
|
Veteran
Join Date: Mar 2009
Posts: 9,893
|
Quote:
Originally Posted by Tom
I'm not entering it - I am getting the times from Wikipedia, and they come in the time of day format. I want to change them to a format I can use, a decimal number.
|
I see - let's say cell B3 is the 2:30.1 output. Have cell C3 = B3 and go to formatting for cell C3 (select "general"). You will now have a decimal number. Go to cell D3 and put in C3 multiplied by 86,400 (which represents 24 hours x 60 minutes x 60 seconds). The output in D3 is what you want. If time is less than one min (for a example a 4.5 fur race), cell B3 would need to be in format 0:52.1, not simply 52.1.
Last edited by Saratoga_Mike; 05-24-2014 at 06:28 PM.
|
|
|
05-24-2014, 07:19 PM
|
#9
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,887
|
Perfect!
It worked!
THANKS!
__________________
Who does the Racing Form Detective like in this one?
|
|
|
05-24-2014, 08:12 PM
|
#10
|
Just another Facist
Join Date: Mar 2002
Location: Now in Houston
Posts: 52,808
|
Quote:
Originally Posted by Saratoga_Mike
I see - let's say cell B3 is the 2:30.1 output. Have cell C3 = B3 and go to formatting for cell C3 (select "general"). You will now have a decimal number. Go to cell D3 and put in C3 multiplied by 86,400 (which represents 24 hours x 60 minutes x 60 seconds). The output in D3 is what you want. If time is less than one min (for a example a 4.5 fur race), cell B3 would need to be in format 0:52.1, not simply 52.1.
|
Bravo Mike! That's some sweet work. Never had a knack for excel. In fact I hate it. But it's an under sung app for sure. In a previous job we had a spreadsheet that was 28 pages that all talked to each other after importing stats from a phone switch and ACD system for a call center. It scared the hell out of me just open the damn thing.........
__________________
WE ARE THE DUMBEST COUNTRY ON THE PLANET!
|
|
|
05-25-2014, 03:23 PM
|
#11
|
Registered User
Join Date: Apr 2014
Location: Flint Hills
Posts: 474
|
If Raybo doesn't know, it can't be done!
__________________
"Better to do little well than more poorly." Appy
|
|
|
05-28-2014, 10:51 PM
|
#12
|
EXCEL with SUPERFECTAS
Join Date: Mar 2004
Posts: 10,206
|
Quote:
Originally Posted by Appy
If Raybo doesn't know, it can't be done!
|
Off hand, I probably would have used formulas to determine the number of hours, minutes, and seconds, then have used a concatenation to put them back together, in the format I wanted, and formatted the result as a decimal number, with specified number of decimal places.
|
|
|
05-28-2014, 10:54 PM
|
#13
|
EXCEL with SUPERFECTAS
Join Date: Mar 2004
Posts: 10,206
|
Quote:
Originally Posted by JustRalph
Bravo Mike! That's some sweet work. Never had a knack for excel. In fact I hate it. But it's an under sung app for sure. In a previous job we had a spreadsheet that was 28 pages that all talked to each other after importing stats from a phone switch and ACD system for a call center. It scared the hell out of me just open the damn thing.........
|
LOL, so the worksheets were actually talking to other worksheets? Freaky!! But, nothing done in Excel really surprises me anymore.
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|