How to Transform Time from Base 60 to Base 10 in Google Sheets Using Apps Script onEdit // MM:SS.MS to SS.MS

Other Questions Google Sheets
1

I am part of a local auto racing team responsible for timing events. Our timing equipment provides results in the format of Minutes:Seconds.Milliseconds (MM:SS.MS). I need to have that time automatically converted into Seconds.Milliseconds (SS.MS) in the same cell for easier calculations, while still allowing input in the SS.MS format.

For instance:
In the sheet named “Time,” columns E through H are where I enter the race times. If I input a time of 1:24.78 in cell E3, I want it to display as 84.78 in that same cell.

I’ve already made a function to convert from MM:SS.MS to SS.MS, but I want it to convert onEdit directly in the cell where I enter MM:SS.MS.

function timeConversion() {
  var timeParts = arguments[0].split(".");
  var minuteSecond = timeParts[0].split(":");
  var milliseconds = parseInt(timeParts[1], 10);
  var totalSeconds = parseInt(minuteSecond[1], 10) + (parseInt(minuteSecond[0], 10) * 60);
  return totalSeconds + '.' + milliseconds;
}
2

Another approach can be to keep your formula-driven conversions and introduce a helper column to dynamically transform the MM:SS.MS into SS.MS without altering the input cell directly. This avoids potential issues or conflicts with the onEdit trigger limitation on direct cell input reformatting. Simply have a hidden column next to your input cells that uses the Apps Script logic and outputs the transformed value in the adjacent cell. For visibility, you can apply conditional formatting to highlight discrepancies between input and output, ensuring racers quickly spot any errors in time entries.

3

To achieve the desired live conversion using Apps Script for the onEdit trigger, you can enhance your current setup by implementing a trigger function that utilizes the timeConversion logic. However, keep in mind that the onEdit trigger works with simple operations such as setting values in the cell, rather than converting the input directly. Here’s a conceptual approach:

function onEdit(e) {
  var sheet = e.source.getSheetByName('Time');
  var range = e.range;
  var input = e.value;

  if (range.getColumn() >= 5 && range.getColumn() <= 8) { // Ensure it's in columns E through H
    if (/^\d+:\d+\.\d+$/.test(input)) { // Regex to match MM:SS.MS format
      var convertedTime = timeConversion(input);
      range.setValue(convertedTime);
    }
  }
}

In this script, I added an onEdit function that triggers whenever a change is made in the specified columns. The regex ensures the format is MM:SS.MS, before applying your conversion function. This should help keep everything automated and seamless during race time inputs!