I’m a heavy user of Google Forms for quizzes in my classes. I have been curious to know how much time my students spend on a quiz, but Google doesn’t have a straightforward way to record this. It does record the time they finish, but not when they start. Thanks to a few web forums with similar questioning, I have developed a way to generate the time a student takes. It does require students to enter the date and time that they start, so it may be off by a few minutes, but it should return reasonable times.
This process will require you to use a Google Sheet to look at your results, but Google makes it easy to connect your results to a Sheet.
Step 1: Add date and time fields to the quiz/form
Add a question for date and a question for time. For Question type, select the Date and the Time types.
Step 2: Google Sheet
Identify and add columns
- Identify the columns that contain the Date and Time responses. (In this example, these are Columns F and G.)
- Identify the columns that contain the automated Timestamp. (In this example, this is Column D.)
- Add a column where you want to calculate the duration of time the student worked on the quiz. In Row 1 label this column “Time on Quiz” (or whatever makes sense to you).
Write the formula
Google uses Row 1 as the header row, so let’s assume that your first data is in Row 2.
Here’s a sample sheet layout
| Row | D | E | F | G |
|---|---|---|---|---|
| 1 | Timestamp | Time on Quiz | Date | Time |
| 2 | 11/2/2023 12:16:25 | 11/2/2023 | 11:57:00 AM |
=D2-(TEXT(F2,"m/dd/yy ")&TEXT(G2,"hh:mm:ss"))Cut and paste this formula, then adjust the cell references as needed. Notice there is a space between the m/dd/yy and the following quotation mark.
Format the new cell
In the Format…Numbers… menu, you should find “Duration.” Select this for the format.
Getting bad results?
I found that the most common mistake was students entering the wrong date. If you see a Duration number that is way too big, check their date column and ‘cut/paste’ the correct date into that cell.
If a student enters the wrong time, you probably can’t fix that, but check to see if they made a mistake with AM/PM.
Screenshots
Acknowledgements
- There are tutorials all over the web for Google Sheets, but I got my formula from 3Pie Analytics.
- This StackExchange post gave me the idea about asking students to enter the date and time.