Running a formula in the background of a cell on Sheets

Question

I am trying to make a sheet where I can type a time (duration) into a cell without colons or decimals and it show up in the same cell with the colons and decimals (example: input "10342" and the cell reads "1:03.42"). The formula I have that works in another cell is:

=ARRAYFORMULA(TEXT(AVERAGE(VALUE(IF(D3<>"", TEXT(
 IF(IFERROR(      LEFT(D3, LEN(D3)-6))="",      0,         LEFT(D3, LEN(D3)-6))&":"&
 IF(IFERROR(RIGHT(LEFT(D3, LEN(D3)-4), 2))="", "00", RIGHT(LEFT(D3, LEN(D3)-4), 2))&":"& 
 IF(IFERROR(RIGHT(LEFT(D3, LEN(D3)-2), 2))="", "00", RIGHT(LEFT(D3, LEN(D3)-2), 2))&"."& 
 IF(LEN(D3)>1, RIGHT(D3, 2), "0"&D3), "[h]:mm:ss.00"), ))), "[h]:mm:ss.00"))

I have tried conditional formatting and I'm not the greatest with macros. Is there anyway that this would be possible?

Answer 1

You clearly need an onEdit function. You have to open the script editor within your spreadsheet file and copy paste the code snippet I provide here. With the following code, every time you edit a cell in column A of "Sheet1" you get back the desired format of your input to the exact same cell. If the value you enter is: 10342 it will become: 1:03.42 . If the value you enter is 130342 you get back 13:03.42 . If you want to edit cells only after row 1 (in case you have a header) you can add in the if condition statement the condition : row >1.

  function onEdit(e) {
  
  var row = e.range.getRow();
  var col = e.range.getColumn();
  
  if (col === 1 && e.source.getActiveSheet().getName() === "Sheet1"){
   
   var valu = e.source.getActiveSheet().getRange(row,1).getValue().toString()
   
   if (valu.length == 5){
    var result =  valu.slice(0,1)+":"+valu.slice(1,3)+"."+valu.slice(-2);
  } 
  else if ( valu.length==6) {
  var result = valu.slice(0,2)+":"+valu.slice(2,4)+"."+valu.slice(-2);
  }
   e.source.getActiveSheet().getRange(row,1).setValue(result); 
  }
  
}

Don't forget to modify the name of the sheet (in my case "Sheet1") as well as the column you want to work with. As you can see my solution uses col===1, e.source.getActiveSheet().getRange(row,1).setValue(result) and var valu = e.source.getActiveSheet().getRange(row,1).getValue().toString(). Number 1 corresponds to column A. If you want to work with column D, for example, you need to replace 1 with 4 for all these formulas. Let me know in the comments if you have any questions.