2

We have cells that store time in the H:MM format (24-hour time without a leading zero for hours). However, I don't like typing values such "8:00". Ideally, I'd like to type just a single character "8" and it'll be converted to "8:00" after hitting Tab or Enter.

Is it possible? Can Google Sheets do it? If not, I'd like to get rid of of the colon, at least, typing just "800".

0

2 Answers 2

3

You can do that with an onEdit(e) simple trigger, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* Converts numbers like 8 and 15 into times like 8:00 and 15:00.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  // version 1.0, written by --Hyde, 19 July 2023
  //  - see https://webapps.stackexchange.com/q/171340/269219
  if (!e || !e.value || !e.value.match(/^(1?\d|2[0123])$/)) {
    return;
  }
  e.range.setValue(e.value + ':00');
}

See Working with date and time values in Google Sheets and Working with Dates and Times in Apps Script.

2

The Issue

In Sheets times and dates are simply numbers that have been "formatted" as such. Where you see 8:00 it is a formatted version of the number 0.3333333333333330.

Reason

  1. Sheets stores numbers with a maximum of 15 digits of accuracy. Additional digits are replaced by 0 (not rounded)
  2. A day is equivalent to the number 1
  3. 8 hours is equivalent or 8÷24
  4. In decimal notation ⅓=0.333... (...=repeating)
  5. Because of point #1 ⅓<>0.333... but instead ⅓=0.3333333333333330`

Solutions

To avoid typing 8:00 you will need to do one of:

1. Equivalent number

Enter a number that is equivalent to 8:00 AM:


 

2. Use a Formula

Enter your numbers in one column and include an ARRAYFORMULA in another to convert to times. For example, the formula below assumes that the integer portion of numbers in column A are hours, 5=5hrs / 0=0hrs, and the decimal portion represents the minutes, 0.15=15min / 0.15<>0.15hrs:

  =ARRAYFORMULA(
  INT(FILTER(A:A,LEN(A:A)))/24+
  MOD(FILTER(A:A,LEN(A:A)),1)/14.4)

Or the same formula using the LET function:

 =LET(val,FILTER(A:A,LEN(A:A)),
    ARRAYFORMULA(
      INT(val)/24 + MOD(val,1)/14.4))

3. Use a Script

doubleunary's answer explains this approach using Google Apps Script.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.