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.
Format the cells using the custom format if there isn’t a “time” option that works for you. MM:SS or :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.
you need to input the time as 00:21:05 and then display it how you wish.
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.
If cell A1 contains the text string “21:05”, you can create the text string “00:21:05” with the formula =concatenate(“00:”,A1)
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.
Excellent. Of course, why use “merge” when a simple term like “concatenate” is available.
Thank you spreadsheet genious.
You could also just put =A1 / 60 in the B column, and that will convert the hh:mm to mm:ss.
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…
If cell A1 contains the text string “21:05”, you can create the text string “00:21:05” with the formula =concatenate(“00:”,A1)
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.
I’ve been looking for this answer for three years (but never thought to post the question among these hooligans). Thanks, Wags.