Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I do triathlons (short ones). For each race I get 6 results:

  • mm:ss for the swim
  • mm:ss for the transition between swim and bike
  • mm:ss for the bike
  • mm:ss for the transition from bike to run
  • mm:ss for the run
  • hh:mm:ss.t for the overall time.

Here are my numbers:

  • 14:03
  • 3:55
  • 43:16
  • 1:34
  • 33:30
  • 1:36:15.6.

When I paste these into Excel, it turns them into date/time values instead of leaving them as elapsed time. For example, the bike time of 43:03 is converted to 1/1/1900 7:16:00 PM.

I have tried various format settings including custom, but those seem to affect the output, not the way the numbers are evaluated when I do the paste.

Can I force them to just remain strings when I do the paste or make it turn the numbers into a number of seconds?

share|improve this question
    
To keep something a string, put a single quote before it. '14:03 –  Robert Kerr May 13 '12 at 2:30
    
You can also do them as seconds, but will require conversions in both directions. Write yourself a function (or search for one, "VBA convert mm:ss to elapsed seconds", or something like that) to convert hh:mm:ss to a number of seconds. Once you have all the times converted to seconds, you can use them for further computations. –  Robert Kerr May 13 '12 at 2:33

1 Answer 1

When you paste a string like 43:03 Excel interprets it as hh:mm. To force interpretation as mm:ss include a leading hours figure: 0:43:03. The underlying value will be converted to a Date Serial number

Then use custom format to display as you want: [mm]:ss to force display of minutes >= 60

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.