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?

Does this answer your question? [Is there a way to evaluate a formula that is stored in a cell?](https://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell) — Rubén, Jul 25 '20 at 04:12

Marios · Accepted Answer · 2020-07-25T13:24:21.927

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.

That worked. Now I am curious on how to get it to work on more than one column. I tried changing the "col === 1" to ">0" but then it stopped working. I am also looking to get averages on these times. With the formula I have listed above, I got a value back that was duration, so when I multiplied it by 86400, it would give me the same time but in seconds, allowing sheets to work out an average. I've tried adding that formula in, but it does not work as script. — Morgan Patrick, Jul 25 '20 at 13:27
In order to apply the same logic for different columns you can either edit the code I provided or duplicate the onEdit function and change the column number. Please accept my answer if it answers your initial question. I will be able to answer your additional questions in a different post. I think your additional questions have to do with the formatting of the spreadsheet file. Please accept my answer if you think it was useful. — Marios, Jul 25 '20 at 13:31
Alright, I got all of that to work. I have it starting at a certain row, is there a way to make it stop doing it at a certain row, like start at row 4 and end at row 9? — Morgan Patrick, Jul 25 '20 at 16:38
Replace the first if statement with this (col === 1 && e.source.getActiveSheet().getName() === "Sheet1" && row>3 && row<10) — Marios, Jul 25 '20 at 16:46

Running a formula in the background of a cell on Sheets

1 Answers1