2

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a "duration" format: 90m, it will automatically convert it to: 1h 30m,

In Google Sheets when I type in 90m, it appears to convert the input to "text" and bumps it to the left of the cell. If I type in: 1h 30m same thing happens. If I type in: 00:90:00, it converts it to: 1h 30m, 1:30, also yields the same result. If you type in 1h 30m, the exact display that it is supposed to show it will not recognize it as a duration - but as text.

Here is a quick example of what I am talking about: Typed was formatted as ="@" so that it would preserve exactly how I typed it in the right column:

Here is a quick example of what I am talking about: Typed was formatted as ="****" so that it would preserve exactly how I typed it in the right column

So we input hundreds of lines of this a day. inputting it all in the xx:xx format is a huge pain in the butt. Is there any way to have Google Sheets recognize 90m, as 90 minutes, and put it in the format it is programmed to?

2 Answers 2

0

At this time Google Sheets doesn't include a way to customize the automatic data type assignation but it's possible to use Google Apps Script to automatically convert input from one type to another but it has some limitations but let start with the simplest solution, then on another Q&A we could talk about how to circumvent the known limitations and the problems that arise for cases of general interest.

It's possible that an add-on already exists. If you need a recommendation post a question on Software Recommendations

General steps

  1. Create an Apps Script project
  2. Add a simple on edit function
  3. Add the JavaScript to convert input in the form of #unit to Google durations
  4. Complete the simple on edit function to return the result to the spreadsheet

Create an Apps Script project

  1. Click on Tools > Script editor
  2. Click on Untitled project or save the project to assign a name to the project

NOTE: If this is your first project and you don't have plans yet to create many, the name could be anything, but a short descriptive name always is better

Add an on edit simple trigger.

  1. Replace the default code by
function onEdit(e){

}

Add the JavaScript code
Complete the simple on edit function to return the result to the spreadsheet

For convenience, I added the code of the last two steps in a single code block.

function onEdit(e) {
  var duration = stringToDuration(e.value);
  if(e.value !== duration) {
    e.range.setValue(duration);
    e.range.setNumberFormat('[h]:mm'); // Here you can set the number format to use
  }
}

/**
 * Converts a string to a Google Sheet duration value
 *
 * @param {String} input The string to be converted
 * @returns An Integer if the input format is supported otherwise returns the input
 */
function stringToDuration(input){
  if(!typeof input === 'string') return input;
  var date = new Date(0);
  var match = input.match(/^(\d.*)(.)/);
  var value = parseFloat(match[1]);
  var isDuration = true;
  switch(input[2]){
    case 'm':
      date.setMinutes(value);
      break;
    case 'h':
      date.setHours(value);
      break;
    default:
      output = input;
      isDuration = false;
  }
  var output;
  if(isDuration) {
    var oneDayInMS = 24*60*60*1000;
    output = date.getTime() / oneDayInMS;
  } else {
    output = input;
  }
  return output
}

REMARKS

  1. The above code just consider two cases, #m (like 90m) and #h (like 1.5h). For a complete solution adapt the code of a JavaScript library like moment.js.
  2. The Google Apps Script triggers works on "best effort" base, this means that triggers like on edit, on form submit, etc. sometimes aren't triggered. I.E. if your type fast many values, some of the edit will trigger the onEdit function but others not, so you could have a time-driven trigger to scan the values that were not converted and then convert them.
  3. onEdit functions on bounded project scripts are available only for the bounded spreadsheet. To make it available to all your spreadsheets create an add-on, install it on your account and enable it on the spreadsheet that you want to use it.
-1

=ARRAYFORMULA(IF(LEN(A55:A), REGEXREPLACE(
 IFERROR(REGEXEXTRACT(SUBSTITUTE(A55:A, "h", ":"), "(\d+):"), 0)+
 IFERROR(QUOTIENT(REGEXEXTRACT(TO_TEXT(A55:A)&"m", "(\d+)m"), 60))&"h "&
 IFERROR(MOD(REGEXEXTRACT(TO_TEXT(A55:A)&"m", "(\d+)m"), 60)&"m", "0m"), "^0h ", ), ))

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.