英文:
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 the 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.
英文:
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.
答案1
得分: 2
从您的以下回复中，
>它们是文本（不是日期对象）；我不想覆盖这些单元格... 而是创建一个新的列，其中包含129.52和22.93。
以下示例公式如何？
=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_2 + res_3))))
- 在此示例公式中，文本
129:31被:分割，并且31被除以60。
英文:
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.
答案2
得分: 0
另一个选择是使用TIMEVALUE函数，它接受一个字符串并将其转换为数字（您可以按数字或日期格式进行格式化，根据您的需求）。由于TIMEVALUE的输入是一个包含小时的字符串，我建议您只需在前面添加0:以添加“小时”：
=TIMEVALUE("0:"&A1)
只是为了向您展示，现在您已经有了正确的日期格式，我在截图中将您提供的两个值相加
如果您只想要按照您的要求的确切数字，将其乘以24（小时）和60（分钟）。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论