Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Four Digits to Time in Google Sheets with Google Apps Script

Google Apps Script, Google Sheets

On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately, the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

The Google Sheets Formula Approach
The Google Apps Script onEdit Approach

The Google Sheets Formula Approach

Let’s take a look at the ‘easier’ Spreadsheet formula-based approach first.

Warning. However, before we continue, this approach does not place the time value in the same cell as the cell where the value was entered. For example, if you entered the 4 digit value ‘1445’ in cell A4, you could not use this formula to put it back in cell A4. You would have to put it in another cell like B4 or wherever you want it placed.

Step 1.

Ensure that the column you will get the 4 digits from has its format set to text. If you don’t do this you will find that some of the four-digit numbers with leading zeroes will have their zeroes removed and cause problems for the formula later.

You can set the format to text by selecting the menu Format > Number > Plain Text.

Step 2

Next, select the cell you want to put the newly changed time into. In our example, we will use the corresponding cell in column B.

In this cell, add the following formula:

=LEFT(A2,2)&“:”&RIGHT(A2,2)

Let’s break this down. LEFT returns the characters starting from the left and going to the right:

=LEFT(reference cell, number of characters from the left)

For example, if we have the four-digit value “1423” in cell A2 and we only want the first 2 digits. we would call =LEFT(A2,2) which would return the value: “14”.

Next, we use the concatenation symbol “&” to add the colon like this: &“:”&.

With our “1423” example, you will have “14:” by this stage.

Finally, we add the right-hand side of the 4 digits with, you guessed it, RIGHT. The RIGHT formula works the same way as LEFT, and takes similar values:

=RIGHT(reference cell, number of characters from the right)

Continuing our “1423”, the RIGHT formula, =RIGHT(A2,2), would display, “23”.

Combining it all together, “1423” is transformed into “14:23”.

As mentioned earlier, the limitations for using this approach are pretty clear; You can’t use this formula to modify the same cell you add information to (Unless you want to add it inside the LEFT and RIGHT formulas) also you cannot set the cell format to time (It remains as a formula).

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

The Google Apps Script onEdit Approach

Using Google Apps Script, we can edit the cell that you are editing (onEdit), and it will return the 4 digits to a time-formatted cell. For example, if you type in ‘1640′ and hit <enter>, it will return ’16:40′, or whatever time format you have set up like, ‘4:40 pm’.

Again, before we start we need to change the format of the column we will edit to ‘text’. We do this to ensure that any leading zeroes are included. You can set the format to text by selecting the menu Format > Number > Plain Text.

Let’s take a look at what we will create:

The Code – Basic

Let’s look at the basic code:

//Get the time from 4 digits. 

//--GLOBALS--
var COLUMN_TO_CHECK = 1;
var SHEET_NAME = "Sheet1";

function onEdit(){
 //Get the active Spreadsheet and Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  //Check you are on the correct Sheet
  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();
    
    
    if(selectedCell.getColumn() === COLUMN_TO_CHECK){
      //Ensure cell is set to text.
      selectedCell.setNumberFormat("@");

      var cellValue = selectedCell.getValue();
      //Spit the first 2 characters and last two and put in a colon ":" in the middle.
      var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
      //Add the newly created value back into the cell. 
      selectedCell.setValue(time);
      //Set the cell format to time. 
      selectedCell.setNumberFormat("hh:mm");
    };
  };
};

//Get the time from 4 digits.

//--GLOBALS--

var COLUMN_TO_CHECK = 1;

var SHEET_NAME = "Sheet1";

function onEdit(){

//Get the active Spreadsheet and Sheet

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

//Check you are on the correct Sheet

if(sheet.getName() === SHEET_NAME){

var selectedCell = sheet.getActiveCell();

if(selectedCell.getColumn() === COLUMN_TO_CHECK){

//Ensure cell is set to text.

selectedCell.setNumberFormat("@");

var cellValue = selectedCell.getValue();

//Spit the first 2 characters and last two and put in a colon ":" in the middle.

var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);

//Add the newly created value back into the cell.

selectedCell.setValue(time);

//Set the cell format to time.

selectedCell.setNumberFormat("hh:mm");

};

Set Globals

In lines 3-6 we set our global variables.

//Get the time from 4 digits. 

//--GLOBALS--
var COLUMN_TO_CHECK = 1;
var SHEET_NAME = "Sheet1";

function onEdit(){
 //Get the active Spreadsheet and Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  //Check you are on the correct Sheet
  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();
    
    
    if(selectedCell.getColumn() === COLUMN_TO_CHECK){
      //Ensure cell is set to text. 
      selectedCell.setNumberFormat("@");

      var cellValue = selectedCell.getValue();
      //Spit the first 2 characters and last two and put in a colon ":" in the middle.
      var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
      //Add the newly created value back into the cell. 
      selectedCell.setValue(time);
      //Set the cell format to time. 
      selectedCell.setNumberFormat("hh:mm");
    };
  };
};

//--GLOBALS--

var COLUMN_TO_CHECK = 1;

var SHEET_NAME = "Sheet1";

Line 4 sets the column we want the code to check for a 4-digit value. Line 5 sets the name of the sheet tab we want to check.

onEdit()

Once we have set up our Global variables, we go ahead and look at our onEdit(e) function (line 7). onEdit(e) is a simple trigger function that is executed when the user enters a selected cell. It takes the argument ‘e‘ which will be the event object, or in this case, the values you will enter in the cell.

Get the Spreadsheet and Active Sheet

Lines 10 and 11 call the spreadsheet ( ss ) and the specific sheet inside it (sheet).

//Get the time from 4 digits. 

//--GLOBALS--
var COLUMN_TO_CHECK = 1;
var SHEET_NAME = "Sheet1";

//Same with limited error handling - cell must be formatted with text.
function onEdit(){
 //Get the active Spreadsheet and Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  //Check you are on the correct Sheet
  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();
    
    if(selectedCell.getColumn() === COLUMN_TO_CHECK){
      //Ensure cell is set to text. 
      selectedCell.setNumberFormat("@");

      var cellValue = selectedCell.getValue();
      //Spit the first 2 characters and last two and put in a colon ":" in the middle.
      var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
      //Add the newly created value back into the cell. 
      selectedCell.setValue(time);
      //Set the cell format to time. 
      selectedCell.setNumberFormat("hh:mm");
    };
  };
};

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

Simple Validation of the Data

//Get the time from 4 digits. 

//--GLOBALS--
var COLUMN_TO_CHECK = 1;
var SHEET_NAME = "Sheet1";

function onEdit(){
 //Get the active Spreadsheet and Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  //Check you are on the correct Sheet
  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();
    
    
    if(selectedCell.getColumn() === COLUMN_TO_CHECK){
      //Ensure cell is set to text.
      selectedCell.setNumberFormat("@");

      var cellValue = selectedCell.getValue();
      //Spit the first 2 characters and last two and put in a colon ":" in the middle.
      var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
      //Add the newly created value back into the cell. 
      selectedCell.setValue(time);
      //Set the cell format to time. 
      selectedCell.setNumberFormat("hh:mm");
    };
  };
};

//Check you are on the correct Sheet

if(sheet.getName() === SHEET_NAME){

var selectedCell = sheet.getActiveCell();

if(selectedCell.getColumn() === COLUMN_TO_CHECK){

//Ensure cell is set to text.

selectedCell.setNumberFormat("@");

The next thing we need to do is to check that the sheet we are working on, the active sheet, matches the sheet we want to work on – identified in our global variables as SHEET_NAME(line 13). To get the name of the active sheet we call sheet.getName().

Line 14 selects the cell we are working on, the active cell.

On line 17 we make sure that we are only editing the column we want to, in our case, column 1 (column A). We do this with another ‘if‘ statement ensuring that if the selected cell’s column (.getColumn) is the same as our COLUMN_TO_CHECK, then we are good to proceed.

In our final bit of data validation on line 19, we ensure that the cell is set to text for editing. We do this by adding .setNumberFormat("@"); to the selected cell, where “@” means that whatever is in the cell is text. There are more format patterns available here.

Convert 4 digits to Time

//Get the time from 4 digits. 
 
//--GLOBALS--
var COLUMN_TO_CHECK = 1;
var SHEET_NAME = "Sheet1";
 
//Same with limited error handling - cell must be formatted with text.
function onEdit(){
 //Get the active Spreadsheet and Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  //Check you are on the correct Sheet
  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();
    
    
    if(selectedCell.getColumn() === 4){
      //Ensure cell is set to text.
      selectedCell.setNumberFormat("@");

      var cellValue = selectedCell.getValue();
      //Spit the first 2 characters and last two and put in a colon ":" in the middle.
      var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
      //Add the newly created value back into the cell. 
      selectedCell.setValue(time);
      //Set the cell format to time. 
      selectedCell.setNumberFormat("hh:mm");
    };
  };
};

var cellValue = selectedCell.getValue();

//Spit the first 2 characters and last two and put in a colon ":" in the middle.

var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);

//Add the newly created value back into the cell.

selectedCell.setValue(time);

//Set the cell format to time.

This is where the magic occurs. First, on line 20, we get the values inside the active cell we are working on. Then, we separate the first and last two digits with a colon. We do this by invoking Javascript’s substr() (Sub String) method which can help us look at certain characters in a string.

Sub String can take one or two arguments. The first argument (substr(start)) is where we would like to find our starting character. In our first use of the Sub String, we want the first value. For example, if we want the first (zeroeth) value of the text, ‘0922’, the starting value would be ‘0’.

The second argument is the length substr(start, length), this argument is optional and, if omitted, will automatically set to the remaining length of the string. For example, if we want the first two characters of this string we would do the following:

var str = '1422';
str.substr(0,2); //=> '14'

1 2	var str = '1422'; str.substr(0,2); //=> '14'

To get the last two characters of a string, we don’t need to invoke the length argument. Instead, we simply call the length of the string minus the value from the right we want to start from. For example:

var str = '1422';
str.substr(str.length -2); // => '22'

1 2	var str = '1422'; str.substr(str.length -2); // => '22'

Back to our code and we can see that we have concatenated or joined (‘+‘) the two Sub Strings and added the colon (“:“) between them to get a time value.

Once we have our new value in our time variable, we set the value into the cell.

Finally, we can now set the format of the cell to hours and minutes (“hh:mm“).

The Code – With Data Validation

Perhaps we need to make some special rules to ensure that the users put in 4 digits that are with time parameters. If there is an error then we will return an error.

Let’s see what we added:

//Get the time from 4 digits. 

//--GLOBALS--
var COLUMN_TO_CHECK = 1;
var SHEET_NAME = "Sheet1";
var HEADER = 1;

function onEdit(e) {
  //Get the active Spreadsheet and Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  //Check you are on the correct Sheet
  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();
    //Make sure data validation does not include header.
    if(selectedCell.getRow() > HEADER){

      if(selectedCell.getColumn() === COLUMN_TO_CHECK){

        //Ensure cell is set to text.
        selectedCell.setNumberFormat("@");

        var cellValue = selectedCell.getValue();
        
        //Get value as number
        if(/^\d+$/.test(cellValue) === true){
          var cellNumber = Number(cellValue);
          if(cellValue.length !== 4){
            //Check total number of digits is 4.
            return selectedCell.setValue("#Err: 4 Nums Needed");
          }else if(cellNumber > 2400){
            //Check total number is not greater than 2400 
            return selectedCell.setValue("Err: Num > 2400");
          }else if(cellNumber %100 > 60){
            //Check last 2 digits are less than 60
            return selectedCell.setValue("Err: Last 2# > 60");
          }
          else{
            //Spit the first 2 characters and last two and put in a colon ":" in the middle.
            var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
            //Add the newly created value back into the cell. 
            selectedCell.setValue(time);
            //Set the cell format to time. 
            selectedCell.setNumberFormat("hh:mm");
          };
        }else if(cellValue === ""){
          // if cell returned to empty, set to text format.
          selectedCell.setNumberFormat("@");
        }else{return selectedCell.setValue("Err: NaN")}; //if not a number 
      };
    }else{
      return;
    };
  };
};

//Get the time from 4 digits.

//--GLOBALS--

var COLUMN_TO_CHECK = 1;

var SHEET_NAME = "Sheet1";

var HEADER = 1;

function onEdit(e) {

//Get the active Spreadsheet and Sheet

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

//Check you are on the correct Sheet

if(sheet.getName() === SHEET_NAME){

var selectedCell = sheet.getActiveCell();

//Make sure data validation does not include header.

if(selectedCell.getRow() > HEADER){

if(selectedCell.getColumn() === COLUMN_TO_CHECK){

//Ensure cell is set to text.

selectedCell.setNumberFormat("@");

var cellValue = selectedCell.getValue();

//Get value as number

if(/^\d+$/.test(cellValue) === true){

var cellNumber = Number(cellValue);

if(cellValue.length !== 4){

//Check total number of digits is 4.

return selectedCell.setValue("#Err: 4 Nums Needed");

}else if(cellNumber > 2400){

//Check total number is not greater than 2400

return selectedCell.setValue("Err: Num > 2400");

}else if(cellNumber %100 > 60){

//Check last 2 digits are less than 60

return selectedCell.setValue("Err: Last 2# > 60");

}

else{

//Spit the first 2 characters and last two and put in a colon ":" in the middle.

var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);

//Add the newly created value back into the cell.

selectedCell.setValue(time);

//Set the cell format to time.

selectedCell.setNumberFormat("hh:mm");

};

}else if(cellValue === ""){

// if cell returned to empty, set to text format.

selectedCell.setNumberFormat("@");

}else{return selectedCell.setValue("Err: NaN")}; //if not a number

};

}else{

return;

};

Don’t include header

Line 6 adds a HEADER global variable. Because we are adding outputs for error handling, we don’t want the header title to be caught in an error so we need to remove the header from the cells to edit. On lines 16 and 17 you can see that if the active cell is in a row that is greater than the height of the header we will only validate the cell.

Is it a number

Next, on line 24 we check to see if the characters inside the text string are numbers using regular expressions. The cell returns the standard NaN, or Not a Number, if the text does not meet this parameter on line 47.

Has 4 digits

Line 26 checks to see if the data the user enters is not exactly 4 characters long. If it is greater or less than 4 digits, then it will return an error, line 28. If there are 4 characters it will move to the next condition…

Cell is not larger than 24 hours.

On line 29 we check to see if the characters we entered are not greater than 2400. To do this, we first must change the 4 character text value to a number using Number(cellValue) creating the cellNumber variable.(line 25). If the value is great than 2400 – because that is not a 24 hr time – then it will display an hour.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

To further check we are using time values, we need to ensure that the last two digits are smaller than 60 (only 60 minutes in an hour, right?) on line 32.

If all parameters are met, then we are good to proceed with adding the data.

Let’s take a look at how the data validation looks.

Enjoy.

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Changelog

2023-06-02

Bug fix where the cell format in selecteCell.setNumberFormat("@") was called before the column was identified causing any edited cell’s format to change when onEdit() is triggered. (Thanks JP for picking this up).

14 thoughts on “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

qw says:

at 10:33 am

Hi, I want to apply this to different 4 column in one sheet.
What do I need to change in the code?

Loading...

Reply
1. Yagi says:
  
  at 5:52 pm
  
  Hi qw,
  
  You could change the COLUMN_TO_CHECK to an array of columns [1,2,4,6] and then try adding an or ( || ) in the if statement that evaluates COLUMN_TO_CHECK:
  
  if(selectedCell.getColumn() === COLUMN_TO_CHECK[0]||selectedCell.getColumn() === COLUMN_TO_CHECK[0]|| ...){ ....
  
  1
  2
  3
  
  if(selectedCell.getColumn() === COLUMN_TO_CHECK[0]||selectedCell.getColumn() === COLUMN_TO_CHECK[0]|| ...){
  ....
  
  Give it a try and let me know how you got.
  
  ~Yagi.
  
  Loading...
  
  Reply

Thank you for your help.

But when I do as you say, I get an error. Can you share the code completely for another columns?

Hi qw,

How about you share what you have so far. Then I can guide you. You’ll learn more from this approach, I’m sure.

~Yagi

I did something like that, still not working.

[code]var COLUMN_TO_CHECK = 1;
var SHEET_NAME = “Sheet1”;

var COLUMN_TO_CHECK2 = 2;
var SHEET_NAME = “Sheet1”;

function onEdit2(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

if(sheet.getName() === SHEET_NAME){
var selectedCell = sheet.getActiveCell();

selectedCell.setNumberFormat("@");    
if(selectedCell.getColumn() === COLUMN_TO_CHECK2){
  var cellValue = selectedCell.getValue();
  var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
  selectedCell.setValue(time);
  selectedCell.setNumberFormat("hh:mm");
};

selectedCell.setNumberFormat("@");

if(selectedCell.getColumn() === COLUMN_TO_CHECK2){

var cellValue = selectedCell.getValue();

var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);

selectedCell.setValue(time);

selectedCell.setNumberFormat("hh:mm");

};

};
};
[/code]

Hi qw,

It looks like you are trying the replicate the entire function (ie onEdit and onEdit2) instead of adding another or (||) value to the if statement. onEdit is a custom trigger that looks at edited items.

How about you change COLUMN_TO_CHECK to an array of values:

var COLUMN_TO_CHECK = [1,3] //These are the columns you want to check

1	var COLUMN_TO_CHECK = [1,3] //These are the columns you want to check

Then you can add it to the “if” statement line I showed you earlier:

if(selectedCell.getColumn() === COLUMN_TO_CHECK[0]||selectedCell.getColumn() === COLUMN_TO_CHECK[0]|| ...){
     ....

1 2	if(selectedCell.getColumn() === COLUMN_TO_CHECK[0]\|\|selectedCell.getColumn() === COLUMN_TO_CHECK[0]\|\| ...){ ....

Have a try implementing that and let me know how you go.

qw says:

at 12:57 pm

I guess I should say I’m new to coding. 🙂 I couldn’t figure out exactly where to put the if command.

var COLUMN_TO_CHECK = [1,2]; var SHEET_NAME = "Sheet1";
function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); if(selectedCell.getColumn() === COLUMN_TO_CHECK[1]||selectedCell.getColumn() === COLUMN_TO_CHECK[2]){ if(sheet.getName() === SHEET_NAME){ var selectedCell = sheet.getActiveCell(); selectedCell.setNumberFormat("@"); if(selectedCell.getColumn() === COLUMN_TO_CHECK){ var cellValue = selectedCell.getValue(); var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2); selectedCell.setValue(time); selectedCell.setNumberFormat("hh:mm"); }; }; }; }

Loading...

Hi qw,

You are almost there. I think you added that if statement instead of changing it:

var COLUMN_TO_CHECK = [1,2];
var SHEET_NAME = "Sheet1";

function onEdit(e){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
<del datetime="2019-04-21T07:13:01+00:00">if(selectedCell.getColumn() === COLUMN_TO_CHECK[1]||selectedCell.getColumn() === COLUMN_TO_CHECK[2]){</del>
if(sheet.getName() === SHEET_NAME){
var selectedCell = sheet.getActiveCell();
selectedCell.setNumberFormat("@");
if(<strong>selectedCell.getColumn() === COLUMN_TO_CHECK[1]||selectedCell.getColumn() === COLUMN_TO_CHECK[2]</strong>){
var cellValue = selectedCell.getValue();
var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);
selectedCell.setValue(time);
selectedCell.setNumberFormat("hh:mm");
};
};
};
}

var COLUMN_TO_CHECK = [1,2];

var SHEET_NAME = "Sheet1";

function onEdit(e){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

<del datetime="2019-04-21T07:13:01+00:00">if(selectedCell.getColumn() === COLUMN_TO_CHECK[1]||selectedCell.getColumn() === COLUMN_TO_CHECK[2]){</del>

if(sheet.getName() === SHEET_NAME){

var selectedCell = sheet.getActiveCell();

selectedCell.setNumberFormat("@");

if(<strong>selectedCell.getColumn() === COLUMN_TO_CHECK[1]||selectedCell.getColumn() === COLUMN_TO_CHECK[2]</strong>){

var cellValue = selectedCell.getValue();

var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.length - 2);

selectedCell.setValue(time);

selectedCell.setNumberFormat("hh:mm");

};

}

qw says:

at 2:06 am

Still not working that code Yagi. 🙂

Loading...

Reply
1. Yagi says:
  
  at 6:58 am
  
  Yep, I also noticed that you were referencing the COLUMN_TO_CHECK array a little wrong. The array is:
  
  var COLUMN_TO_CHECK = [1,2];
  
  1
  
  var COLUMN_TO_CHECK = [1,2];
  
  To get the first value (zeroeth) value of the array you would:
  
  COLUMN_TO_CHECK[0] //result is 1
  
  1
  
  COLUMN_TO_CHECK[0] //result is 1
  
  For the next element in the array you would do:
  
  COLUMN_TO_CHECK[1] //result is 2
  
  1
  
  COLUMN_TO_CHECK[1] //result is 2
  
  Try modifying that part of the code. I’ve tested it and it works fine with that modification.
  
  Good luck.
  
  Loading...
  
  Reply
Didier says:

at 12:06 am

Hi,

your code works perfectly, and i used it in one of my sheets, i thank you for it.

But i’ve got one problem :

Now i must be able to also enter negative values in the cells that are converted with your script.
For example, on monday i worked two hours more so i enter 0200 and your script convert it to 02:00 which is good
but on tuesday i worked one hour and fourteen minutes less so i enter -0114 (ou -114) and the script returns an error.

I tried to find a workaround, even by trying to convert the positive value converted by the script afterward but to no avail ?

Would you have any solution ?

Thank you.
Didier.

Loading...

Reply
Pingback: javascript - Convert numbers to time in google spreadsheets - educationame
JP says:

at 10:03 pm

It should be note that in your code when you ensure the cell value is in plain text format ,you’ve only check for the row but not && columm. This mean the force format will apply outside of your intented columm.

Loading...

Reply
1. Yagi says:
  
  at 12:08 am
  
  Great find on an old post. Updated with thanks.
  
  Loading...
  
  Reply

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

The Google Sheets Formula Approach

The Google Apps Script onEdit Approach

Changelog

2023-06-02

Like this:

Related

14 thoughts on “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

Leave a ReplyCancel reply

The Google Sheets Formula Approach

The Google Apps Script onEdit Approach

Changelog

2023-06-02

Share this:

Like this:

Related

14 thoughts on “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

Leave a ReplyCancel reply