Google Forms – How to Track Time Taken to Fill Out a Form

formulasgoogle sheetsgoogle-formsgoogle-sheets-arrayformula

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.

Best Answer

A sneaky way would be to ask the respondent to enter a current time as the first question of your form and the actual submission time will act as the end time. Then you just run a simple ARRAYFORMULA in your spreadsheet to get the duration:

=ARRAYFORMULA(IF(LEN(A20:A), A20:A-B20:B, ))

Formula

=ARRAYFORMULA(IF(COLUMN(B1:S1)=ROW(A2:A19), 0, ""))

Explained

The IF statement, in combination with the ARRAYFORMULA, validates whether the column indices match the row indices. When they do, show a zero and in all other cases, just show nothing.

Note

Now that you have the solution you want, you're not able to make use of it by adding something in that range. If you do, it will destroy the ARRAYFORMULA. Making a print-out seems like the only benefit.

Google-sheets – Is it possible to link a Google Form to a desktop Excel sheet so that when someone fills in the form the data goes into the Excel sheet

As far as I am aware, this is not possible, given Excel's incompatibility with Google Apps Script.

There is an alternate and only slightly less efficient solution, though: each time you want to analyse the results of your Form, simply download the connected Google Sheet as an Excel spreadsheet, open the downloaded spreadsheet in Excel, and do your analyses as per usual. To download the Google Sheet as an Excel file, go to File -> Download as -> Excel spreadsheet (.xlsx) in Google Sheets.

Best Answer

Related Solutions

Google-sheets – How to fill the diagonal of a matrix with zeros

Formula

Explained

Note

Google-sheets – Is it possible to link a Google Form to a desktop Excel sheet so that when someone fills in the form the data goes into the Excel sheet

Related Topic