Google-sheets – How to sort spreadsheet by date+time column but with a modified hour column

google sheetssorting

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.

Best Answer

Short Answer

=Array_Constrain(ArrayFormula(INT(A2:A)+(H2:H)),COUNT(A2:A),1)

Explanation

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.

Related Solutions

Google-sheets – Forcibly format column as Date in sheets onOpen() with Javascript

The method setNumberFormat does the job:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("C:C");
  range.setNumberFormat("yyyy/MM/dd");
}

This formats column C as yyyy/MM/dd. Many other date formats are available: the method setNumberFormat supports Java's SimpleDateFormat.

Google Sheets – Capture Created Date from Editable Form

This is possible with a script. You'll need to choose a column in your spreadsheet in which the first submission time will be stored. Below, I suppose this is Column D (i.e., 4th column). When a form is submitted, the script checks whether the corresponding cell in the "first submission" column is empty. If it's empty, the timestamp is copied there. Otherwise, the script does nothing.

This logic is contained in the function recordFirstTime. The other two functions are technical: run "Install" to install the script (it will prompt for authorization). If you want to stop the script from recording first submission time, run "Uninstall".

function recordFirstTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getLastRow();
  var timestamp = sheet.getRange(row, 1);   // automatic timestamp
  var record = sheet.getRange(row, 4);   // stored timestamp, edit to change the column
  if (!record.getValue()) {
    timestamp.copyTo(record);
  }
}

function Install() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("recordFirstTime").forSpreadsheet(ss).onFormSubmit().create();
}

function Uninstall() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }
}