Minutes and seconds in Excel

  QuizMan 09:47 19 May 10
Locked
Answered

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 10

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

=TIME(0,HOUR(A1),MINUTE(A1))

and format as Time hh:mm:ss

to get the time as mm:ss.

  QuizMan 11:13 19 May 10

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 10
Answer

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 10

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.

AMD Ryzen release date, specifications and features: Three CPUs from the Ryzen 7 range now…

1995-2015: How technology has changed the world in 20 years

Glassworks Barcelona shows how it captured the artistic imagination of Conor in A Monster Calls

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…