If You Record XC Results on an Excel Spreadsheet...

Excel spreadsheets work pretty well for entering competitors, schools, and times for a cross country meet--except for one thing. Here's how to fix that one thing.

The Excel spreadsheet is a pretty good tool for recording people, teams, places, and times (not so good, of course, for doing team scoring--for that, there is meet manager software, but that is a different topic for a different article). Well, Excel is almost good for recording times. 

The one issue with recording times comes up when you have a time of 24:00 or greater. Up to this point, both minutes and seconds and hours and minutes are written the same way, as two numbers separated by a colon.

Excel, however, always assumes you mean hours and minutes, and not minutes and seconds. So, when you enter a race time of 24:00 or higher, it converts that time into something different. It's aggravating. Some folks "get around" this by separating the minutes and seconds with a decimal point instead of a colon. So, 24:03 becomes 24.03. It sort of works, but 24.03 means 24.03 seconds, not 24 minutes and three seconds. Other folks just omit the colon in between. This also sort of works but begs for a more elegant solution.

Some people try other workarounds, but they all leave a kluged kind of feeling in your stomach.

Your more elegant solution is this:

1. Before you start entering race results on the spreadsheet, select the entire column that will have the times entered into it. You do this by clicking on the very top of the column. You know you have the column selected when the entire column highlights. If you try to do this after you have started entering times in the spreadsheet, this will mess up the times you have entered, so make sure to do it first.

2. Now select Format/Format Cells/Text. This will cause all entries into this column to be treated as text instead of times or numbers. The beauty of this is that Excel doesn't try to interpret what you enter as text. It just leaves it as text. You can enter times all day, even times over an hour, and Excel won't modify any of them.

Problem solved.

I trust this will prove useful to somebody out there in cross country scoring land this fall :-).

 

Note: There are more sophisticated ways to get an Excel spreadsheet to understand a time entry as minutes, seconds, and decimal parts of a second. If you know Excel well enough to make it sing this particular song, great. If not, the solution above works very nicely for the casual user.

Comments