CODEWITHSUNDEEP
google-sheetstimesumgoogle-sheets-formuladuration
Francisco Maria Calisto
Francisco Maria Calisto

Reputation: 3240

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

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?

Upvotes: 0

Views: 5139

Answers (5)

2ple
2ple

Reputation: 11

Putting this on here if anyone wants a more intuitive answer.

=MINUTE(A1) * 60 + SECOND(A1)

This grabs the minute column and the second column and multiplies the minute column by sixty. This could also be done for hours if desired, and the coefficient would be 60*60, or 3600.

=HOUR(A1) * 3600 + MINUTE(A1) * 60 + SECOND(A1)

Hope this is helpful!

Upvotes: 1

Climbs_lika_Spyder
Climbs_lika_Spyder

Reputation: 6714

I use division by the TIME function. To get the number of seconds, you can simply divide your duration by TIME(0,0,1).

Here is an example of how I use TIME(): worksheet

Columns D, E, and F come from a Google Form I use for my employees. I calculate G by subtracting the beginning and end times and then subtracting the lunch minutes. This is my first use of the TIME function. The data format for G is Duration.

G2 =(E2-D2)-TIME(0,F2,0)

The H column is an easy number in this example. Everyone is paid fairly; no one works for $10/hour. In reality, this column pulls from another sheet. Lastly, I is the number of hours worked times dollars per hour.

I2 =G2*(H2/TIME(1,0,0))

Using the TIME() function is really helpful for me.

Upvotes: 1

CmdrKeene
CmdrKeene

Reputation: 131

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

Upvotes: 1

TheMaster
TheMaster

Reputation: 50406

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.

Upvotes: 2

player0
player0

Reputation: 1

use:

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

enter image description here

Upvotes: 0

Related Questions