I want to be write time in any format in cell.

1. If I write 1.5 then cell shows me 1:30 when I write 1:30 it shows 1:30 but doesn't shows the one for 1.5 like wise below

2. If I write 36.25 then cell shows me 36:15

3. If I write 64 then cell shows me 64:00

I can do this in excel using VBA with worksheet change event but how it needs to be done in Google sheets. Does anybody know the answer to this question. I would be grateful for any help.

CodePudding user response：

Use an onEdit(e) simple trigger and simple math, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
        'It runs automatically when you hand edit the spreadsheet. '
        'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  timeEntry_(e);
}

function timeEntry_(e) {
  if (!Number(e.value)) {
    return;
  }
  e.range.setValue(e.value / 24).setNumberFormat('[h]:mm');
}

See this answer for an explanation of how date and time values work in spreadsheets.