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

35 views0 comments

Recent Posts

See All