How to convert duration of MM:SS to seconds in Google Sheets?

Question

As duration of a task, suppose that we have a column with cells formatted as MM:SS that would like to convert to seconds. Mathematically speaking, the solution is simple. We just need to compute MM * 60 + SS converting it to seconds. Additionally, we followed this strategy to solve the problem. However, the direct way to solve it remains a question.

What is the direct way to do it?

Whirly

The accepted answer at your link appears to be a fairly "direct" way, if your values are time values (and not just text strings). Could you edit your question to clarify what you mean by direct, and what kind of data you have? – Peremptory

By direct, it means the proper way. The solutions presented in that link do not provide enough context for this problem. Indeed, the provided and right answers are quite different. – Whirly

Answer 1

use:

=INDEX(QUERY(SPLIT(TO_TEXT(A1:A), ":")*{60, 1},
 "select Col1+Col2 label Col1+Col2''"))

Chromatid

Thank you for your solution, but we are facing a problem now. The error says Unable to parse query string for Function QUERY parameter 2: Can't perform the function sum on values that are not numbers on Google Sheets. – Whirly

@FranciscoMariaCalisto can you share a copy of your sheet? – Chromatid

Sorry, it was our fault. It was a missed cell. Solved now. Thank you for your solution. – Whirly

If you could provide some further explanation concerning your answer, it would be great. Good explanations make your answer even more important. – Whirly

Answer 2

All values are stored as days. 1 is one day. So the direct way to get seconds is just multiply by 24 hours/day, 60 minutes/hour and 60 seconds/minute. For eg, for 1 minute:

="0:1:0"*24*60*60

The underlying format is always HH:MM:SS. For MM:SS, reduce the factor by 60:

="01:01"*24*60

Note: This works because 1 hour in minutes is equal to 1 minute in seconds. But 01:01 by default, is considered 1 hour and 1 minutes by Google.

Sirree

We just need the MM:SS format, which is commonly different from the solutions we found. Yet, thanks for your answer. – Whirly

@FranciscoMariaCalisto Just remove a *60. It should work for MM:SS. – Sirree

Not really. The solution would be to remove both *24 and *60, while adding the +SS amount. – Whirly

@FranciscoMariaCalisto Check my edit. It should fully work now. – Sirree

It's great now! Much more clear. Thanks! – Whirly

Answer 3

You have hours:minutes and want to convert it to just be total seconds?

The answer is to multiply the time value (which is whole day increments) by 24 (hours per day), then by 60 (minutes per hour) and finally 60 again (seconds per minute). This assumes the times are stored in Excel time formatting (I use the time format with the 37 hours example in the dialog box, then removed the seconds from the custom string).

Example

Industrious