At times, your dataset may contain all the necessary details for your analysis. However, more often than not, you’ll need to manipulate the dataset to extract the specific information required to achieve your desired results, particularly when working with time data.
Working with time data often involves the need to convert units for effective analysis. If you find yourself dealing with seconds in your dataset, but your analysis requires observations at the minute level, fear not! Google Sheets provides powerful tools to help you convert seconds to minutes seamlessly.
In this post, you will learn various methods and functions available in Google Sheets that will allow you to effortlessly convert seconds to minutes and make your time-based analysis more efficient and accurate.
Convert Seconds to Minutes by Dividing with 60
You can convert seconds to minutes using a simple method that involves dividing the number of seconds by 60. Each minute consists of 60 seconds, so dividing the total number of seconds by 60 gives us the equivalent number of minutes. By dividing by 60, you can obtain the equivalent time value in minutes.
To apply this method in Google Sheets, simply reference the cell containing the time in seconds and divide it by 60.
Another option for performing the same calculation is to utilize the DIVIDE function. This function operates with two parameters: the dividend and the divisor. The dividend parameter represents the value that needs to be divided, while the divisor parameter indicates the value by which the division is carried out. By utilizing the DIVIDE function in Google Sheets, you can easily convert between seconds and minutes.
Convert Seconds to Minutes with the CONVERT Function
The CONVERT function in Google Sheets converts between measurement units effortlessly, and all you need to do is provide each measure’s unit of identification. This versatile function takes several arguments and can be used to convert values to a variety of different units of measure, including converting seconds to minutes.
=CONVERT(value, start_unit, end_unit)
The parameters supported by the CONVERT function include:
value– the number whose unit of measurement you want to convert.
value’scurrent unit of measurement.
end_unit– the unit of measurement to which you want to convert the
end_unit arguments must be in text and quotation marks.
=CONVERT(B4, “sec”, “min”)
To convert from seconds to minutes with the CONVERT function, use the syntax above. When using the CONVERT function, make sure to use units of measurements recognized or supported by Google Sheets.
In this syntax, “sec” and “min” are the recognized measurement units for seconds and minutes in Google Sheets. Anything outside this and the syntax will return an “Invalid units for conversion” error.
Convert Seconds to Minutes with a Custom Date and Time Format
The Custom date and time format feature in Google Sheets provides a flexible approach to presenting dates and times according to your specific requirements.
While it doesn’t directly convert seconds to minutes, it allows you to display the converted values in an easily understandable duration format. By utilizing the Custom date and time format, you can tailor the presentation of time data to suit your needs, enhancing clarity and usability in your spreadsheets.
To use the custom date and time format, divide the value by 60, then by 1440. When you divide a value by 60 and then by 1440, you are essentially converting the value from seconds to days.
Dividing by 60 converts the value from seconds to minutes, as there are 60 seconds in a minute. Then, dividing by 1440 converts the value from minutes to days, as there are 1440 minutes in a day (24 hours x 60 minutes).
To obtain the result of the calculation in minutes, you can utilize the Custom date and time format by following these steps:
- Select the cells containing the values you want to convert to minutes.
- While keeping the cells selected, go to the Format menu, click on Number, and select Custom date and time.
- Clear the contents in the Custom date and time formats text box using the Backspace or Delete buttons, then click on the dropdown arrow and select Hour.
This will add the Hour component to the text box.
To display leading zeros in the hour component of your time format, you can click on the Hour section and choose the option “Hour with leading zero (01)”. This ensures that the hour value will be displayed with a leading zero if it is a single-digit number.
After making this selection, you can add a colon (:) to separate the hour component from the minute component, creating the desired time format.
Repeat these steps until you have all components in the text box filled in. Then click on the Apply button.
The selected cells will now display their values with the applied custom date and time format.
Convert Seconds to Minutes with the QUERY Function
The QUERY function in Google Sheets is a powerful tool that allows you to retrieve and manipulate data from a specified range or table. It is similar to the SQL SELECT statement, and it can be used to filter, sort, summarize, and format data.
=QUERY(data, query, [headers])
The QUERY function has the following parameters:
data– the range of cells containing the values you want to query.
query– the computation you want to carry out on data written in Google Visualization API Query Language.
headers– the number of rows of data used as column headers.
You can use the QUERY function in two ways – it can serve as an alternative to the custom date and time format, as well as replace the CONVERT or division by 60 methods explained earlier.
=QUERY(QUERY(QUERY(B3:B8, "SELECT B/60/1440", 1), "FORMAT Col1 'hh:mm:ss'", 1), "LABEL Col1 'Time (in mins)'")
To QUERY in place of the custom date and time method, paste this syntax into an empty cell.
The syntax involves nesting multiple QUERY functions within each other to perform data manipulations and formatting. Let’s break it down step by step:
1. The innermost QUERY function
QUERY(B3:B8, "SELECT B/60/1440", 1) – This function selects the range B3:B8 and divides the values by 60 to convert them from seconds to minutes. It then further divides the results by 1440 to convert minutes to days. The `1` denotes that the data has headers.
2. The middle QUERY function
QUERY(QUERY(B3:B8, "SELECT B/60/1440", 1), "FORMAT Col1 'hh:mm:ss'", 1) – This syntax takes the output of the innermost QUERY function and applies formatting to the resulting column, “Col1”, using the specified format “hh:mm:ss”. This format represents hours, minutes, and seconds. Again, the “1” indicates that the data has headers.
3. The outer QUERY function:
QUERY(QUERY(QUERY(B3:B8, "SELECT B/60/1440", 1), "FORMAT Col1 'hh:mm:ss'", 1), "LABEL Col1 'Time (in mins)'") – The outermost QUERY function takes the output of the middle QUERY function and adds a label to the resulting column. It replaces the default column label – Col1 – with “Time (in mins)” to provide a more descriptive header for the column.
Overall, the syntax performs a series of calculations and formatting operations on the range B3:B8. It converts the values from seconds to minutes, formats the result as a time value, and adds a custom label to the column.
=QUERY(QUERY(B3:B8, "SELECT B/60", 1), "LABEL Col1 'Time (in mins)'")
If you want to use the QUERY function in place of the CONVERT function, this is the syntax to use.
Unlike the first QUERY syntax, this one consists of only two nested QUERY functions. Here’s a breakdown of it:
1. The inner QUERY function
QUERY(B3:B8, "SELECT B/60", 1) – This function operates on the range B3:B8. The “SELECT B/60” query divides each value in column B by 60, effectively converting the values from seconds to minutes. The parameter “1” represents the header row, indicating that the data has a header.
2. The outer QUERY function
QUERY(QUERY(B3:B8, "SELECT B/60", 1), "LABEL Col1 'Time (in mins)'") – The result of the inner QUERY function serves as the input range for the outer QUERY function. The `”LABEL Col1 ‘Time (in mins)'”)` query is applied to the output of the inner QUERY. It labels the first column (Col1) as ‘Time (in mins)’, providing a more descriptive header for the data.
This syntax performs a double-query operation on the range B3:B8 where the inner query divides the values in column B by 60 to convert them from seconds to minutes. The outer query then adds a custom label to the resulting column, making it clear that the values represent time in minutes.
Convert Seconds to Minutes with the MOD and QUOTIENT Functions
The MOD and QUOTIENT functions in Google Sheets perform division operations just like the DIVISION function. However, they return different outputs. The MOD function returns the remainder of a division operation, while the QUOTIENT function returns the integer portion of a division operation.
=MOD(dividend, divisor) =QUOTIENT(dividend, divisor)
Both functions have similar parameters – dividend and divisor.
=QUOTIENT(B4, 60)&":"&MOD(B4, 60)
Use this syntax to convert from seconds to minutes.
The syntax combines the QUOTIENT and MOD functions in Google Sheets to convert the time from seconds to minutes. Let’s break it down:
QUOTIENT(B4, 60) – The QUOTIENT function divides the value in cell B4 by 60 and returns the whole number portion of the operation. This calculation represents the minute’s portion of the time.
"&":"&" – The ampersand (&) is used to concatenate or join strings in Google Sheets. In this case, it concatenates the colon (“:”) symbol between the minutes and seconds portions of the time.
MOD(B4, 60) – The MOD function calculates the remainder when the value in cell B4 is divided by 60. It returns the remaining seconds after dividing by 60. This calculation represents the second portion of the time.
By combining these functions and using the concatenation operator, the syntax creates a formatted time string that displays the converted value in minutes and seconds.
Convert Seconds to Minutes with the TIME Function
The TIME function in Google Sheets allows you to create time values based on specific hours, minutes, and seconds. It enables you to generate custom time entries or perform time-related calculations within your spreadsheet.
By utilizing the TIME function, you can accurately represent time values in a format that suits your needs.
=TIME(hour, minute, second)
TIME takes the individual portions of time as parameters – hour, minute, second. It uses values specified in these parameters to create time values in the spreadsheet.
=TIME(0, 0, B4)
Use this query to convert time from seconds to minutes.
The function constructs a time value with the given parameters: 0 hours, 0 minutes, and the value in cell B4 as the seconds.
In this post, various methods of converting seconds to minutes have been explored. From converting seconds to minutes using simple division to utilizing the powerful QUERY function for data extraction, Google Sheets offers a wide range of tools to convert time from seconds to minutes.
Whether it’s the custom date and time formatting, utilizing the CONVERT function, or constructing time values with the TIME function, these methods provide flexibility and efficiency when working with time-related data. By leveraging these functionalities, you can effectively analyze, transform, and present your time data in a way that meets your specific needs.
If you find this helpful, let us know in the comments!