I have a spreadsheet generated by a Google form and it produces a standard spreadsheet with a full timestamp column, that is two hours behind, and an hour column (hh:mm am/pm) that the user fills in manually.

I need to apply the hour that the user has filled to the date column and sort the result, displaying in a different sheet and updating values as more forms are submitted.

Here's a copy of the spreadsheet.

migrated from superuser.com Jan 10 '16 at 0:44

This question came from our site for computer enthusiasts and power users.


Short Answer



  • INT(A2:A) removes the time part of the timestamp.
  • +H2:H adds the time captured by the respondent
  • Array_Formula() makes that the inner functions returns array of values.
  • COUNT(A2:A) counts the number of form submissions.
  • Array_Constrain() limits the results the number of form submissions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy