With the upcoming 94.7 cycle challenge we thought we would give you a quick overview of how Excel works with time. Like dates, Excel sees time differently from a human. So if you want to analyse time splits in Excel, you need to understand how Excel works with time and, for longer events where the total time goes over 24 hours, how to correctly format the time.
How Excel sees time
For Excel, time is a number from 0 to 1 which tells Excel what percentage of the day has gone by. So if you want to tell Excel 18h00, you can just as easily say 0.75 (18 hours / 24 hours).
Below some idea of what you as a human want to see, and what Excel sees.
If you type a number like 0.75 into a cell and then format it as time you will see it turns into a time in your desired format.
So given that time is merely a number, it is easy to understand how you can generate time splits in Excel by simply adding and subtracting these ‘numbers’ and then formatting them to represent time.
Example of Time Splits in Excel using 94.7 cycle challenge points on route
Let’s say we want to work out (ignoring difficulty for the moment) what splits we need to be on at each point of the route so that we can get a time of 5 hours (or less).
First we need to know how to tell Excel we mean 5 hours.
Type the time into Excel
You can enter it but you need to be careful. If you type into a cell 05:00:00 (note the first 0), then Excel should recognise it as time and format it as such. But it doesn’t always work when you type it in like this and depends on your regional settings. To check whether it is a proper Excel time, format it back to numbers and you should see a decimal number.
Calculate the number with maths
As mentioned above, time is just a number that represents a percentage of a 24 hour day. So if I can work out the decimal value that 5 hours represents out of a 24 hour day then I can enter this into Excel and then format as time.
So if I want 5 hours, I could type in =5/24 and the resultant decimal will be recognised by Excel as the time 5 hours. If it shows 0.2083333 then you just need to format it to time.
If you want to get down to a specific hour, minute and second, then you just need to work out the appropriate decimal.
There are 86 400 seconds in a day (24 hrs x 60 minutes x 60 seconds). If I want to know the decimal for 05:10:05 it would be the seconds up to that point i.e.
- (5 hours x 60 minutes x 60 seconds)+
- (10 minutes x 60 seconds) +
- 5 seconds =
- 18605 seconds out of a total of 86 400 seconds so
- it is 0.215336….
Format this as time and you will see you get the correct time
Easiest way- Use Excel’s function for time
As much fun as the above calculation is, the easiest way is to use Excel’s TIME function. The function is
=TIME(hour, minute, second)
and it will convert this into the correct Excel friendly time.
So for the above 5 hours, 10 minutes and 5 seconds we would enter =TIME(5,10,5) and it would work it out. This is the safest way to enter time into Excel.
Using calculations to determine the time splits
So getting back to the 94.7, below we have shown the key points in the race and in column B the kilometres between each point. So from the start to Heartbreak Hill is 13 kilometres.
Column C shows the percentage of the total distance covered in that stage e.g. from Heartbreak Hill to Woodmead Drive is 15.08% of the total distance.
Column D shows the cumulative percentage distance up to that point so at the Nelson Mandela bridge/ Queen Elizabeth junction you would have traveled 51.34% of the total distance.
If you look at column E, the formula is just multiplying the cumulative distance by the desired time. So we just treat them as normal numbers (column H shows what Excel is seeing). The only thing we do then is format column E into Time format so that instead of seeing 0.029 like Excel sees in cell H6, we see 00:41:43 for the time we need at Heartbreak Hill.
Column F just works out the time between each point and all you do is subtract the one split from the other.
So time is just a number that you can treat like any other number. The only issue is to format it.
Clock Time vs Stopwatch time
There is one further thing to watch out for but it hopefully won’t apply to your 94.7 ride because it only becomes an issue when total time goes over 24 hours.
Depending on your version of Excel, when you format into time you will see you have limited options in the time category
All the options above are clock formatting. What that means is that it treats the time as a clock.
So if I am racing the Tour De France and I use this format, whether my time is 12 hours or 36 hours, it will show it as 12:00:00. That’s because once a clock hits midnight it restarts at zero (even though Excel will know that you have been going for more than a day).
So if you want Excel to keep adding the hours past 24 hours you need to use a special format found in the Custom format section.
As shown below, in Custom Format there is a format that is [h]:mm:ss . The [h] tells it to just tell you the total hours without resetting the ‘watch’ every 24 hours.
Good luck for your race.