Calculating time taken on a Google Form

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

1. Identify the columns that contain the Date and Time responses. (In this example, these are Columns F and G.)
2. Identify the columns that contain the automated Timestamp. (In this example, this is Column D.)
3. 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.

---