Summing Values in Google Sheets – How to Sum Values Based on Matching First Two Characters

I couldn't find the trick for the real date cell to work yet, but you could have a work around if nothing else by adding the the single quote (') before the date in a format: YYYY-MM-DD in the Q4 cell.

That single quote means that the it will be parsed as string.

You could use an onEdit trigger for the script. That will update whenever changes are made. For example:

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function onEdit(e){
  var copyFromRange = 'Sheet1!A13:A'; // no row for second cell reference
  var copyToRangeStart = 'Sheet2!A3';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

See https://developers.google.com/apps-script/understanding_triggers for more details on triggering scripts automatically.

I can't comment yet, so will ask here: Do you need to copy to a second spreadsheet or just a second sheet within the same spreadsheet? The second case is answered above, but I can adjust if you need to copy to a separate spreadsheet?

edit: added code to fully answer the question. Thanks for the guidance Jacob.