Minutes and seconds in Excel

  QuizMan 09:47 19 May 2010

I organise a series of athletics races for my running club and use Excel 2003 to record the finishing times of the competitors.

Times are always under one hour, but I have yet to find a way to record this in Excel without having to use the format hh:mm:ss, ie I must input a zero for the hour every time. If I use mm:ss, I get the wrong figures in the cell. For example, a time of 25 mins 20 secs shows as 20:00. I can continue to use the hh:mm:ss formula, but, with 100 runners details to record, it causes delay.

Also, if I may throw in one additional question, is there a way to use an alternative separator to the colon which requires use of the shift key and yet more typing? If I could use either a full stop of forward slash, I could input the data via the numeric keypad.

  VoG II 10:37 19 May 2010

You can enter a time in A1 as 22:30 (22 hours 30 minutes). Then in a spare column use


and format as Time hh:mm:ss

to get the time as mm:ss.

  QuizMan 11:13 19 May 2010

VoG™ - thank you for your quick response. Interesting and works up to a point, but not if the race time is more than 24 minutes, presumably because hours can only go as far as that.

It is a 5km race series and sadly, as we get older, not everyone can run it in under 24 minutes.

  VoG II 11:31 19 May 2010

Try this: enter 2530 (no colon) in A2.

In B2

=(ROUNDDOWN(A2,-2) / 2400 + MOD(A2,100) / 1440)/60

Format as Time.

  QuizMan 11:40 19 May 2010

VoG™ - absolutely splendid. Not only does it save me having to input the hours, it also solves the second part of my initial question about the need for use of the colon. Thank you very much.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy S9 review

Wacom Cintiq Pro 24 and 32 review – hands-on

When is the next Apple event?

Gmail : comment annuler l’envoi d’un e-mail ?