I’m a member of a local auto racing club and I’m in charge of timing. My timing equipment outputs time in a MM:SS.MS format. I need the spreadsheet to automatically convert this input to a SS.MS format so I can do calculations easily. I also want to maintain the ability to enter times directly in SS.MS.
For instance, if I input 1:24.78 in a cell, it should change to 84.78 automatically.
I have a function that converts MM:SS.MS to SS.MS, but I want to trigger this conversion automatically on editing certain cells. Here is a modified code snippet that might help:
function autoConvertTime(inputStr) {
let parts = inputStr.split('.');
let timeParts = parts[0].split(':');
let ms = parts[1];
let minutes = parseInt(timeParts[0], 10);
let seconds = parseInt(timeParts[1], 10);
let totalSeconds = minutes * 60 + seconds;
return totalSeconds + '.' + ms;
}
How can I integrate this so that when I enter a time in a specific range, it auto-converts on edit?
hey mate, i’ve got a slightly different approach that might work for ya. instead of changing the cell directly, why not use a formula in the next column? something like =IF(REGEXMATCH(A1, “^\d+:\d+.\d+$”), MINUTE(A1)*60+SECOND(A1), A1) could do the trick. this way you keep your original data intact. just a thought from my experience with timing systems!
I’ve dealt with similar timing issues in my work with rally events. Here’s a slightly different approach that might be more robust for your needs:
Instead of modifying the cell value directly, consider using a separate column for the converted times. This way, you maintain the original input and have a calculated column for your SS.MS format.
In your script, you could use something like this:
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
if (sheet.getName() == ‘Timing’ && range.getColumn() == 1) {
var input = range.getValue();
var output = convertTime(input);
sheet.getRange(range.getRow(), 2).setValue(output);
}
}
function convertTime(input) {
var parts = input.split(‘:’);
if (parts.length == 2) {
var minutes = parseInt(parts[0], 10);
var seconds = parseFloat(parts[1]);
return (minutes * 60 + seconds).toFixed(2);
}
return input;
}
This keeps your original data intact and provides a calculated column for easy use in formulas. It’s been a reliable setup in my experience with timing systems.
As someone who’s worked extensively with timing for motorsports events, I can definitely help you out with this. Your approach is on the right track, but let me share a slightly different method that’s worked wonders for me.
Instead of using a separate function, you can integrate the conversion directly into the onEdit trigger. This way, it’ll automatically convert any time entered in MM:SS.MS format to SS.MS, but leave SS.MS entries untouched. Here’s a snippet that should do the trick:
function onEdit(e) {
var range = e.range;
var value = range.getValue();
var timePattern = /^([0-9]{1,2}):([0-9]{2})\.([0-9]{1,3})$/;
if (typeof value === 'string' && value.match(timePattern)) {
var parts = value.split(':');
var seconds = parseInt(parts[0], 10) * 60 + parseFloat(parts[1]);
range.setValue(seconds.toFixed(2));
}
}
This script will automatically trigger whenever you edit a cell, convert MM:SS.MS to SS.MS if it matches the pattern, and leave other formats as is. Just make sure to set up the correct number formatting in your sheet to display the results properly. Hope this helps streamline your timing process!