I am trying to record the time it takes for a number of things to occur, and can't seem to get Google Sheets to understand my input. I want to be able to type something like "26:30" into a cell, and have the spreadsheet understand that this means 26 minutes and 30 seconds, and then be able to use that number in formulas, e.g. to return the shortest of a series of times, or the difference between two times.

Also, the vast majority of the numbers I type in will be under an hour, so I don't want to have to type in something like "0:26:30" every time, just for it to understand that I mean 26 minutes, not 26 hours. However for the rare occasions where something is longer than an hour, I want to be able to be able to type something like "1:10:23" and not "70:23".

If possible, I would rather achieve this through directly formatting the cell I type the time into, rather than enter it in one format and have it converted in a separate cell via a formula.

Is there a way to do this that meets all of these goals?

CodePudding user response：

google sheets is not designed in such a way. if you want to type in 26:30 then the best course of action is as follows:

convert cells to Plain Text, type in your duration, and account for your rules within formulae. to convert a text string into a value for the sake of calculation you can use the following principle

=ARRAYFORMULA(IFERROR(IF(REGEXMATCH(A1:A, ":\d :"), A1:A*1, ("0:"&A1:A)*1)))

few examples:

=ARRAYFORMULA(TEXT(AVERAGE(IF(REGEXMATCH(A1:A2, ":\d :"), 
 A1:A2*1, ("0:"&A1:A2)*1)), "[m]:ss"))

=ARRAYFORMULA(TEXT(SUM(IF(REGEXMATCH(A1:A2, ":\d :"), 
 A1:A2*1, ("0:"&A1:A2)*1)), "[h]:mm:ss"))

=ARRAYFORMULA(TEXT(IF(REGEXMATCH(A2, ":\d :"), A2*1, ("0:"&A2)*1)-
                   IF(REGEXMATCH(A1, ":\d :"), A1*1, ("0:"&A1)*1), "[m]:ss"))

CodePudding user response：

At this point in time there doesn't seem to be a way to directly format a cell such that Google Sheets recognises it as MM:SS instead of HH:MM, so I've had to go with formulas instead. Sharing the solution I used below.

I set it up so that the user entered the time into cell A1 in the form MM:SS or H:MM:SS and I formatted this cell as plain text, then had a second cell formatted as 'time duration' where it converted the input of A1 using the following formula:

=time(left(A1,if(len(A1)<6,"",len(A1)-6)),right(left(A1,len(A1)-3),2),right(A1,2))

To break this down:

• It starts by assuming the contents of A1 is a text string with 5 or more characters in the form MM:SS or H:MM:SS or HH:MM:SS. It does not include any sort of error handling to check this is true.
• The time(X,Y,Z) part of the formula converts different inputs into hours, minutes and seconds, respectively, and produces a number in the format HH:MM:SS, which is recognised as a time, and can therefore be used in formulas. Note that the cell has to be formatted as 'time duration' to display correctly.
• In the above, Z is right(A1,2) which extracts the last two characters of A1, i.e. the SS part of the input. These end up as seconds.
• Likewise, Y is right(left(A1,len(A1)-3),2) which extracts the 4th and 5th characters from the right of the text string of A1, i.e. the MM part of the input, after the :. This number is then recognised as minutes.
• Finally, X is left(A1,if(len(A1)<6,"",len(A1)-6)), which basically says "if A1 is less than 6 characters then it must not have hours, so leave blank, otherwise extract out all characters except the last 6", i.e. the HH part of the input, if it exists. This number (which may or may not be 0) is then recognised as hours.