-1

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.

Rakshitha
  • 15
  • 1
  • 8

1 Answers1

2

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.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • Let me try. I will let you know tomorrow – Rakshitha Nov 06 '22 at 17:49
  • it worked. But how can I make this work for specific cell or range. As presently it converts every number in the whole sheet to hh:mm – Rakshitha Nov 09 '22 at 08:23
  • Use `e.range.rowStart`, `e.range.columnStart`, `e.range.getA1Notation()` and/or `e.range.getSheet().getName()` together with the equality operator or the inequality operator or `String` methods such as `match()` to check whether the edit took place in the sheet and cell you want to watch. Please ask only [one question per post](https://meta.stackexchange.com/a/222741). In your [new question](https://webapps.stackexchange.com/questions/ask), show what you have tried. See [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/a/261593/13045193) – doubleunary Nov 09 '22 at 09:18