将单元格格式设置为分钟:Google电子表格中的秒 [英] Format cell as minutes : seconds in google spreadsheets

问题描述

我希望记录有关完成某个编程问题所花费的时间的数据.我正在使用Google电子表格来保持性能.面临的问题是，我希望单元格接受结果为 mm:ss ，但是Google表格将其转换为 12小时格式，即 hh :mm:ss ，如何阻止这种情况的发生?

I wish to record data on the amount of time it takes for me complete a certain programming problem. I am using google spreadsheets to maintain my performance. The issue am facing is that, I want the cells to accept the result as mm:ss, but google sheet is converting it to 12-hr format i.e. hh:mm:ss, how can I stop this from happening?

例如:- 1:30 转换为 1:30:00 AM .

推荐答案

更新:

我找到了一种新的方法来做，实际上确实为您提供了所需的价值:

I found a new way to do it that actually does give you the value you want:

=TEXT(TIME(,text(A1,"HH"),text(A1,right(A1,2))),"HH:MM:SS")

查看图片进行转换:

上一个答案-不能完全解决:

因此，您要做的是使用单个正则表达式替换函数，即可将其成功转换为持续时间值:

So what you want to do is using a single regex replace function you can successfully transform it to a duration value:

=REGEXREPLACE(text(A2,"hh:mm:ss"),"(\d+):(\d+):(\d+)","$3:$1:$2")

使用regexreplace我基本上是将每个段分组并重新排序，以便将小时视为分钟，将分钟视为秒.

Using regexreplace I am basically grouping each segment and reordering them so that it treats the hour as the minute and the minute as the seconds.

对于上下文和概念验证，以下是屏幕截图:

For context and proof of concept here is a screenshot:

在单元格A2中，您会看到原始的1:30，如果您在编辑栏上看到google仍将其格式化为12小时.

In cell A2, you see the original 1:30 and if you look at the formula bar you can see that google is still formatting it as a 12 hour time.

要重申上述事实，在BEFORE下右侧的单元格中，您看到我将原始值格式化为文本，以表明它确实按小时，分钟，秒的顺序解释了hh:mm:ss的那些值.

To reiterate the above fact, in the cell to the right under BEFORE you see I format that original value as a text to show that it does interpret those values of hh:mm:ss in the order of hour, minute,second

C2具有对其进行转换的公式

C2 has the formula to transform it

最后，D2指向转换后的公式以证明它实际上已经将其解释为hh:mm:ss了，但分钟和秒的顺序现在在正确的位置

and finally D2 is pointing to the transformed formula to prove that it does in fact now interpret it as hh:mm:ss but the order of minutes and seconds are now in the right places

这篇关于将单元格格式设置为分钟:Google电子表格中的秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！