Entering time data in Excel quickly

The Curzon Kid 23:26 01 Aug 09
Locked

I have a spreadsheet that calculates results for my cycling club time trials that entails a lot of input of times. I was wondering if there was a way that I could speed up this process by formatting the relavant cells to accept input of hh:mm:ss by omitting the colon so that I just have to enter the numbers e.g. hhmmss.
Part of my sheet needs to subtract an age allowance from the time achieved in the race. For example if a rider has an allowance of say 1:00:00 and they actually clock 00:59:00 they would have achieved a "Plus" of 1 minute. If on the other hand they recorded a time of 01:01:00 then they would have achieved a "Minus" of 1 minute. I am using the 1904 date system to display negative times.
My main calculation sheet uses LOOKUP to refer to another worksheet that contains a table of allowances for riders of a given age.
I also need to calculate team times - generally 3 riders in a team.
Another requirement sometimes is to calculate the improvement in a riders Personnel Best Time.
Has anyone come up with a solution that will allow me to do this?

OTT_Buzzard 00:17 02 Aug 09

I can't think of a way to do it directly without a macro. However, indirectly you can use the following set-up:

enter time as hhmmss in cell a1. Put cell a1 format to text.

In cell B1 enter: =left(a1,2)&":"&mid(a1,3,2)&":"&right(a1,2)

Put b1 cell format to time format hh:mm:ss

VoG II 06:12 02 Aug 09

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

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

New Corel ParticleShop plugin now available: 11 new brushes & 6 new brush packs

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…