Google Sheets: How to Convert Column Values In mmm:ss or mm:ss Format to Minutes
I currently have a "minutes" column that has values which look like 129:31 or 22:56
The numbers to the left of : are minutes and the numbers to the right of : are seconds. In Google Sheets, how do I take that column of values and convert it to total number of minutes instead?
For example 129:31 would become 129.52 minutes and 22:56 would become 22.93 minutes. The reason I need to do this is because I want to use my new column in a SUMIF statement.
I've tried format mm:ss but it doesn't return numbers that I can sum.
Solution
From your following reply,
they are the text (not a date object); I don't want to overwrite those cells ... create a new column with 129.52 and 22.93 instead.
how about the following sample formula?
=BYROW(A1:A,LAMBDA(range,IFERROR(LET(res_1,SPLIT(range,":"),res_2,INDEX(res_1,1,1),res_3,INDEX(res_1,1,2)/60,res_1 + res_3))))
- In this sample formula, the text
129:31is split by:and31is divided by60.
- Adding a hyperlink to an inline image on Google Appscript
- Setting min max values for the Horizontal Axis on a Google Sheets chart
- How to match invisible new lines?
- convert two columns containing group and items in group into a single column
- QUERY function to create pivot table with strings
- Remove lines from text in a cell that match a set of words
- how to construct and write a formula to google sheets to perform a dynamic date calculation
- Trying to update Google Sheet data with Python (mathematical expression), but apostrophe keep coming out
- Copy certain cells over to another sheet when the script is running
- google sheets, conditional formatting based on row numbers
- How to prepare string to paste in multiple Google Sheets cells skipping some cells?
- Sorting by Custom Array
- Getting an Undefined Value error in Google Script Function
- Which function runs when there are multiple script projects bound to the same Google Sheet?
- Bringing up data from another SpreadSheet with arrayformula, query and importrange
- Count number of pairs, triplets, etc in a google spreadsheet
- How to get current active/triggered trigger ID ,
- Remove duplicate values from text, ignoring case (keeping original)
- Google App Script - Json response parsing
- How to ignore hidden rows?
- Using INDIRECT in Google Sheets to reference to reference a table's named column
- Run a function published in Webapp from a Google site
- Querying a Google Sheet API using GViz
- Google Sheets query, calculate time
- How do you resolve a "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues" error
- How do i make 3 column to arrange in 1?
- Google Apps Script getBackgrounds for colour custom function
- Auto-change or Query function Google sheets
- What is the purpose of the "UPLUS()" function in Google Sheets?
- How to extract the whole match and not just the captured group?