# Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in 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

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 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 cellnumber 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 cellnumber 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).

### 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, 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:

Set Globals

In lines 3-6 we set our global variables.

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

Line 8 and 9 call the spreadsheet ( `ss` ) and the specific sheet inside it (`sheet`).

Simple Validation of the Data

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 15 selects the cell we are working on, the active cell.  On line 18 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.

Our final bit of data validation on line 20 is to 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.

Convert 4 digits to Time

This is where the magic occurs. First, on line 21, 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:

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:

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:

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 line 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 are 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.

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.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

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

1. qw says:

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

1. Yagi says:

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:

Give it a try and let me know how you got.

~Yagi.

2. qw says:

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?

1. Yagi says:

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

1. qw says:

I did something like that, still not working.

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

function onEdit(e){
var sheet = ss.getActiveSheet();
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”);
};
};
};

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

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

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

};
};
[/code]

1. Yagi says:

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:

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

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

1. qw says:

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"); }; }; }; }```

2. Yagi says:

Hi qw,

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

3. qw says:

Still not working that code Yagi. 🙂

1. Yagi says:

Yep, I also noticed that you were referencing the COLUMN_TO_CHECK array a little wrong. The array is:

To get the first value (zeroeth) value of the array you would:

For the next element in the array you would do:

Try modifying that part of the code. I’ve tested it and it works fine with that modification.

Good luck.

4. Didier says:

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.