I am trying to use A Google Sheet generated by Google Forms to track total attendance time in lab for a robotics team. I have created a form with the following questions:

  1. Checking in or Checking out
  2. Check In Name
  3. Check Out Name

After selecting the first option, the respondent is directed to select their name from a pulldown list of all members. This generates a workbook similar to this workbook. The first sheet includes the responses with the timestamp which is important for recording times. I am thinking the second sheet (Hours) could be used to summarize time.

Is it possible to use a combination of IF and VLOOKUP to calculate hours per day? Or is there a more logical way to accomplish this?

1 Answer 1


Make a pivot having team members as rows, Check in and Check out as columns and the put the timestamps con the corresponding intersections, them add a column to calculate the difference between both timestamps and display the result as hours.

There are several ways to do the above:

  • Use a pivot table and a calculated field
  • Use QUERY function to make the pivot then add a column to calculate the hours per day.
  • Use a combinations of formulas to create the row / column headings, set the intersection values and to calculate the hours per day.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.