Export to Google Sheets and Time Formatting
Updated: Oct 27, 2021
Exporting Data from Flats or Spikes
Exporting your collect split times from Flats or Spikes is easy. You simply go to a meet and export the data. We create a spreadsheet for you, and your data automatically appear. Here is a video that shows the process to get to the export.
Video: Navigating to Export Data
Google Sheets is a little bit funny with date-time formatting. As scientists, we curate data as best we can. We export to Google Sheets for archival purposes and for people to do their own calculations with the data.
Most people do not realize this, but when you type in split times in Google Sheets like this 05:21 - the values automatically change into an hour-minute format following HH:MM when someone was intending the data to be minute-seconds. We have found that if you enter the data in the format 00:05:21 - this automatically sets the data as hh:mm:ss with the data showing up correctly as minutes and seconds. This format is correct. Since Flats or Spikes is used for timing events longer than an hour, we must ensure that we follow this format and do exports correctly.
We fundamentally believe you should never type data into spreadsheets — this is why we built Flats or Spikes — but if you want to use our export functionality for archival purposes or ancillary calculations, we want to make sure this can be done correctly.
Show Google Sheet Data in MM:SS Format
Post-September 14th, 2021
Google sheets data is now directly exported in this format.
Pre-September 14th, 2021
You can do this in a number of ways — but essentially you are just changing the format of the display. To make your data show up in MM:SS format, one has to go to the format menu → number → custom format then type into that space MM:SS. All of your data will show up in the MM:SS format everyone likes to display.
Video: Change format of data to MM:SS
Calculating Split Times from Flats or Spikes Export Google Sheets
Post - September 14, 2021
We adjusted the export of data to google sheets to be formatted directly as duration and shows up in mm:ss format for times less than an hour and hh:mm:ss for times greater than an hour.
Pre - September 14, 2021
The old way: The best way to do additional calculations with our spreadsheet data is to format the data first then calculate. If you calculate first, Google Sheets will give you a value in decimal value (decimal days) which is correct but also must be changed to the MM:SS format for proper display. If you format the data properly and then calculate, Google Sheets will carry the number formatting through correctly.
Video: Showing Format and Calculation in Google Sheets
Copy to Excel and Formatting to MM:SS
Excel handles the copy to Excel much better and automatically recognizes the hh:mm:ss format when copying from Google Sheets, where Flats or Spikes automatically downloads data. You then have to set the data format to display in MM:SS data format.
Video: Showing Copy to Excel and Formatting in Excel