Calculating Vets Standard Times in Excel

If you are using Microsoft Excel to calculate your result sheet - here's a tip for calculating vets times.  As you probably know, Excel doesn't handle negative times very well - so when you subtract one time from the other and the result is a "minus" it fails to display properly.  You have to use some roundabout methods to get at the time.

Here's a very simple, but little known, solution:

• With your results spreadsheet open - go to the Tools menu
• Select Options
• Then select the Calculation tab
• Near the bottom it says "1904 date system" - make sure that is ticked
• Click OK

• Enter the rider's time in the result column in the correct format, which should be HH:MM:SS
• (Note, even if it's a ten mile race you must enter the hours, eg 0:23:25 - otherwise Excel mistakes the minutes for hours which will lead to problems)
• You will already have the rider's standard in another column - this also must be in HH:MM:SS format
• In another column subtract the rider's time from their standard - riders are usually faster than the standard so this will usually be a plus, which doesn't cause any issues with Excel.  
• Any riders slower than the standard will have "minuses", which should be properly displayed if you have followed the steps above.
• Excel will usually also know that this is a time and put it in the correct format (HH:MM:SS) - if it doesn't, go to Format - Cells, click the Number tab and select the Time category
• If you sort the result using the calculated "plus/minus" column in descending order you will get a vets result in the correct order.