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".

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

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))
``````