Is there a way to get Excel to see 21:05 as 21 minutes and 5 seconds rather than 9:05 pm or whatever? I've played around with cell formatting but cant seem to find anything that works well. Some work fine until you get a time over 25:00. I basically want it to see minutes and seconds rather than time of day.
Triathlon Forum
Login required to started new threads
Login required to post replies
Re: help: posting times in spreadsheet [Pony Boy]
[ In reply to ]
Format the cells using the custom format if there isn't a "time" option that works for you. MM:SS or [H]:MM:SS or however you want to do it. If you do it like that you can calculate paces and stuff based off those times if want.
Re: help: posting times in spreadsheet [Pony Boy]
[ In reply to ]
you need to input the time as 00:21:05 and then display it how you wish.
Re: help: posting times in spreadsheet [Kilt]
[ In reply to ]
Tthat's fine except when you are using race data that has been input by someone else. I know how to break apart data (using "text to columns"). How do you merge data? (I could make a column with just "00:" and merge it with existing data.
Re: help: posting times in spreadsheet [Pony Boy]
[ In reply to ]
If cell A1 contains the text string "21:05", you can create the text string "00:21:05" with the formula =concatenate("00:",A1) [in B1]
Then =value(B1) gives you what you are looking for.
Of course, you can combine these two stpes in a single formula if you like.
Then =value(B1) gives you what you are looking for.
Of course, you can combine these two stpes in a single formula if you like.
Re: help: posting times in spreadsheet [Wags]
[ In reply to ]
Excellent. Of course, why use "merge" when a simple term like "concatenate" is available.
Thank you spreadsheet genious.
Thank you spreadsheet genious.
Re: help: posting times in spreadsheet [Pony Boy]
[ In reply to ]
You could also just put =A1 / 60 in the B column, and that will convert the hh:mm to mm:ss.
Re: help: posting times in spreadsheet [Pony Boy]
[ In reply to ]
For my results archive...I convert the entire formatting over to text to avoid problems. Further, I cut and paste the online data into NotePad before pasting into Excel.
This is....assuming I do not use calculations and the results have been properly added.
On another sheet, I then create calculations using formatting above....
This is....assuming I do not use calculations and the results have been properly added.
On another sheet, I then create calculations using formatting above....
I've been looking for this answer for three years (but never thought to post the question among these hooligans). Thanks, Wags.