PDA

View Full Version : Time in Excel


Tom
05-24-2014, 04:25 PM
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?

OverlayHunter
05-24-2014, 05:08 PM
Hopefully someone will know the absolute best solution but I've converted the time to text in the past.

Saratoga_Mike
05-24-2014, 05:20 PM
Why wouldn't you just convert the time to seconds (e.g., 2:04 = 124)?

FocusWiz
05-24-2014, 06:12 PM
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.

Dark Horse
05-24-2014, 06:53 PM
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).

Tom
05-24-2014, 06:57 PM
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.

Dark Horse
05-24-2014, 07:01 PM
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?

Saratoga_Mike
05-24-2014, 07:26 PM
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.

Tom
05-24-2014, 08:19 PM
Perfect!
It worked!

THANKS!

JustRalph
05-24-2014, 09:12 PM
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.........

Appy
05-25-2014, 04:23 PM
If Raybo doesn't know, it can't be done! ;)

raybo
05-28-2014, 11:51 PM
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.

raybo
05-28-2014, 11:54 PM
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.