Take the 2-minute tour ×

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


