Time in Excel

[Tom]

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]

Hopefully someone will know the absolute best solution but I've converted the time to text in the past.

[Saratoga_Mike]

Why wouldn't you just convert the time to seconds (e.g., 2:04 = 124)?

[FocusWiz]

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]

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]

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]

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]

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]

Perfect!
It worked!

THANKS!

[JustRalph]

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]

If Raybo doesn't know, it can't be done! ;)

[raybo]

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]

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.