To calculate average survey completion time, you can export the survey data as a CSV file and open the file in Excel. From there, use the values in the ResponseDate and CompletionDate columns to figure out what portion of a day participants spent on the survey. Then, multiply these portion values by a multiplier to figure out how many seconds each participant took. The sum total of all those seconds, divided by the number of participants, is the average survey completion time in seconds.
Note: This method works best for participants who completed the survey in one sitting. If someone completes part of the survey, goes away, and returns to complete the other portion at a later date, their completion time would include all the time that passed in the interim. You can choose to filter these outliers out of your data if this is the case.
Detailed Steps
- In the Surveys app, open the survey.
- Click Report and open the standard report.
- On the toolbar, click Export > CSV (Response Data), and choose your export settings.
- To see if exports are ready to download, check for a green dot next to Export on the report toolbar.
- When the download is complete, in the All Active Exports dialog, click Download and then follow your browser's prompts to download the file.
- Open the file in Excel.
- Beside the ResponseDate and CompletionDate columns, add an empty column and call it CompletionTime.
- In the CompletionTime column, calculate "CompletionDate minus ResponseDate" for each participant.
This will give you the completion time in days. - Select the CompletionTime column, right-click, and select Format Cells.
- On the Number tab, select General and click OK.
The numbers change from a date format to numbers with multiple decimal places. - Beside the CompletionTime column, add another column to calculate completion time in seconds and call it CompletionTimeSeconds.
- In the CompletionTimeSeconds column, multiply each cell from the CompletionTime column by 86,400.
This value represents 24 hours in a day, multiplied by 60 minutes in an hour, multiplied by 60 seconds in a minute, to give you the total number of seconds. - Select the CompletionTimeSeconds column, right-click and select Format Cells.
- On the Number tab, select General and click OK.
The numbers change from a date format to numbers with multiple decimal places. - Use the SUM function to add up all the CompletionTimeSeconds values.
- Divide the sum value by the number of participants.
This gives you the average survey completion time in seconds. - If you want to go one step further, divide the number from step 16 by 60 to calculate the average number of minutes.