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:
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?
Best Answer
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
#unit
to Google durationsCreate an Apps Script 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.
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.
REMARKS
#m
(like 90m) and#h
(like 1.5h). For a complete solution adapt the code of a JavaScript library like moment.js.