Google Sheets: How to Convert Column Values In mmm:ss or mm:ss Format to Minutes

Question

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.

Answer 1

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:31 is split by : and 31 is divided by 60.

Answer 2

Another option is to use TIMEVALUE which takes a string and converts it to a number (you can format it as a number or as date, if you want). Since the input of TIMEVALUE is a string with hours, I suggest you just add a 0: to add the "hours":

=TIMEVALUE("0:"&A1)

Just to show you that now you have a correct date format, I summed in the screenshot both values you provided

If you just want the number exactly as you requested, multiply it by 24 for hours and 60 for minutes