TIme Tracker Script Output to HH:mm

Question

I have some buttons in my spreadsheet that are connected to a time tracker script, which outputs the current time into the appropriate column:

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

function start() {
  ss.getRange(ss.getActiveRange().getRowIndex(), 5).setValue(new Date().toLocaleTimeString());
  ss.getRange(ss.getActiveRange().getRowIndex(), 5).setNumberFormat('HH:mm');
}

function stop() {
  ss.getRange(ss.getActiveRange().getRowIndex(), 6).setValue(new Date().toLocaleTimeString());
  ss.getRange(ss.getActiveRange().getRowIndex(), 6).setNumberFormat('HH:mm');
  var newcell = ss.getRange(ss.getActiveRange().getRowIndex()+1, 5);
  if (newcell.isBlank()){
    ss.setCurrentCell(newcell).activate();
  }
  else{
  ss.insertRowAfter(ss.getActiveRange().getRowIndex());
  ss.setCurrentCell(newcell).activate();
  };
}

This used to work perfectly and output the time as a number, which I could then format however I needed. I have it written to output the desired format using setNumberFormat('HH:mm'). But just this year after coming back to it after a couple months, it now outputs as text only. For example, instead of outputting 16:00, it outputs 4:00:00 PM as text that I can't work with or use as a reference in a formula. All my formulas calculating total time now give #VALUE!. I have not edited the script at all.

Does anyone know why it's doing this all of a sudden and how I can fix it? Thanks in advance.

Answer 1

You could try using the options argument from toLocaleTimeString()

Try replacing:

toLocaleTimeString()

With:

toLocaleTimeString("en-US", { hour12: false })

Output:

Reference:

• toLocaleTimeString()