CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsautocompletetime-formatautocorrect
Caleb W.
Caleb W.

Reputation: 11

How can I make an autocomplete setting for a custom time format in google sheets?

I need to manually input time data (hh:mm). To make the process quicker I want the cell to autocomplete when I hit enter. For example, when I type 7 in a cell, I want it to autocomplete to 7:00. Or if I type 7:3 it should autocorrect adding the 0 to the end making it 7:30. Is this possible?

I am currently using a custom date format dd/mm/yyyy and if I type 01/12/22, upon hitting enter, google sheets autocorrects the cell to fit the proper format 01/12/2022.

None of the provided autocomplete options will autocomplete/correct a custom time format. As far as I can figure out, the next step is using google scripts to program a custom format or autocomplete setting if that's even possible. Thanks guys

Upvotes: 0

Views: 142

Answers (2)

SvendK
SvendK

Reputation: 475

I have made a time registrations sheet with From, To, Sum and Total columns. Sum is To minus From, and Total is Sum plus Total from the previous row. So as I'm writing, Total adds up.

To and From have a custom format 00":"00. This way I can enter "312" which is shown as 03:12, or "10", shown as 00:10. It doesn't add the remaining 0, as you asked, but how should it know if "35" should be 3:50 or 0:35. And you have to enter an integer; if you enter "3:50" with a colon, it ends up as 00:00. This works for me, anyway. But we're not done yet, as we cannot make calculations (directly) on this text string.

I therefore have two hidden From and To columns with the following formulas: E3 From: =(INT(A3/100)/24)+((A3 - (INT(A3/100)*100))/1440) F3 To: =(INT(B3/100)/24)+((B3 - (INT(B3/100)*100))/1440)

These are the real From and To decimal values, formatted as "hh:mm". 9:52 = 0,41.

The Sum field is then calculated using the hidden values: Sum: =IF(OR(A3="";B3=""); ""; (F3-E3))

And the Total is calculated like: Total: =IF(C3<>"", IF(D2="Total",0, D2) + C3, "")

I hope this helps you out - although you might have figured something out by now :-) If this do answer your question, please mark as answer.

Sheets

Upvotes: 0

Gustavo
Gustavo

Reputation: 714

There isn’t a direct way to achieve that only using Sheets cell formats and relying on the “auto-fix” of values.

However using Apps scripts, you can certainly set up a logic to treat values whenever a certain range of cells is edited.

I’d recommend looking up the Simple Trigger onEdit(e).

onEdit(e) passes a contextual event object e which contains information about the range of cells being edited.

With that information you can determine if the cell being edited is expecting a Time/DateTime value, parse the input to check if it should be “fixed” and push the “fixed” value to the cell.

Sample Implementation:

function onEdit(e) {
  if (e.range.getNumRows() != 1 || e.range.getNumRows() != 1){ // if you are editing more than one cell...
    return; // Exit method execution
  };
  
  if (e.range.getA1Notation() == "E5"){ //if the cell being edited is E5
    var newVal = e.value;

    if(newVal.includes(":")){ //If the new value contains ":"
      var timeList = newVal.trim().split(":"); //Split hour/minute from the new val
      if(timeList[0].trim().length == 1) {//If hour has only 1 digit
        timeList[0] = timeList[0].trim().padStart(2, '0'); // Add a "0" in the beginning 
      }
      if(timeList[1].trim().length == 1) {//If minute has only 1 digit
        timeList[1] = timeList[1].trim().padEnd(2, '0');// Add a "0" in the end 
      }
      //Checking if hour/minute values are "out of bounds"
      if(parseInt(timeList[0], 10) > 23) {//If hour is greater than 23
        timeList[0] = "00"; //defaulting to "00"
      }
      if(parseInt(timeList[1], 10) > 59) {//If minute is greater than 59
        timeList[1] = "00"; //defaulting to "00"
      }
      e.range.setValue(timeList.join(":"));
      return
    } else {
      var tempIntHour = parseInt(newVal.trim(), 10);
      if (isNaN(tempIntHour)) { //If newVal does not contain ":" AND is not a number
        return; // Exit method execution
      }
      //Otherwise, it is a valid integer
      var newTime = `${tempIntHour.toString().padStart(2, '0')}:00`; //Padding with "0" in case it is only one digit
      e.range.setValue(newTime);
      return;
    }
  }
}

Upvotes: 0

Related Questions