blocks|key|324779|text|输入值为0:mm:ss，格式为[m]:ss|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|324780|由于现在以分钟和秒为单位，简单的算术运算将允许您计算统计数据|324781|entityMap^0|4|7|F|6|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

enter the values as <code>0:mm:ss</code> and format as <code>[m]:ss</code>

as this is now in the mins &amp; seconds, simple arithmetic will allow you to calculate your statistics

blocks|key|324829|text|如果您使用的是手工输入的数据，您可以根据您的语言/区域选择而不是00:mm:ss，以mm:ss,0或mm:ss.0格式输入数据。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|324830|如果您希望看到所有分钟秒格式而不是小时分钟秒格式，则需要将单元格格式指定为[m]:ss。|324831|entityMap^0|W|8|16|7|1E|7|0|11|6|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

If you are using hand inputted data, you can enter your data as <code>mm:ss,0</code> or <code>mm:ss.0</code> depending on your language/region selection instead of <code>00:mm:ss</code>. 

You need to specify your cell format as <code>[m]:ss</code> if you like to see all minutes seconds format instead of hours minutes seconds format.

blocks|key|324898|text|作为文本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|324899|=CONCATENATE(TEXT(cell;"d");"+days+";TEXT(cell;"t");"+hours+";MID(TEXT(cell;"hh:mm:ss");4;2);"+minutes+";TEXT(cell;"s");"+seconds")|code-block|syntax|javascript|324900|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

as text:

<pre><code>=CONCATENATE(TEXT(cell;"d");" days ";TEXT(cell;"t");" hours ";MID(TEXT(cell;"hh:mm:ss");4;2);" minutes ";TEXT(cell;"s");" seconds")
</code></pre>

blocks|key|325037|text|5.在“设置单元格格式”框中，单击“类别”列表中的“自定义”。6.在格式列表顶部的“类型”框中，键入h:mm;@，然后单击“确定”。(+h后面是冒号，mm后面是分号。)然后，YOu可以增加小时数。下次需要时，该格式将出现在类型列表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|325038|来自微软的，效果很好。|325039|http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx|offset|length|325040|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|2A|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@$F|R|G|S|1|T]]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|E]]]]

5.In the Format Cells box, click Custom in the Category list. 
6.In the Type box, at the top of the list of formats, type [h]:mm;@ and then click OK. (That’s a colon after [h], and a semicolon after mm.)
YOu can then add hours. The format will be in the Type list the next time you need it. 

From MS, works well. 

<a href="http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx" rel="nofollow">http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx</a>

blocks|key|542142|text|在Excel中输入运行时间的一个方便的技巧是在分钟数之前有两个零和一个冒号，详细信息如下。对于复制和粘贴到Excel中的操作，根本不必担心格式问题，可以使用00:+XX+:XX格式，其中XX是两位数字，总和小于60。在这种情况下，Excel将在显示的单元格内容中回显0:XX:XX，并将数据存储为12:XX:XX+AM如果将00:XXX:XX格式的数据粘贴到Excel中，或者将XX+>+59的00:XX:XX格式的数据粘贴到Excel中，这将被转换为一天的一小部分。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|542143|例如，00:121:12变成0.0841666666666667，如果乘以一天中的秒数86,400就变成7272秒。接下来，00:21:12默认情况下会显示0:21:12存储为12:21:12+AM最后，00:21:60变成了0.0152777777777778，也是一天的零头。|542144|此建议只是为了避免担心Excel中的特定格式，并让程序担心这一点。注意，对于内部格式为12:XX:XX+AM的Excel数据，用户只能使用某些Excel命令，例如，可以取平均值。但是，只有当结果为正数时，减法才起作用。使得建议将时间转换为秒、一天的分数或其他实数，以便访问更完整的算术运算覆盖范围。|542145|例如，如果有一个混合时间格式的列，或者时间是负数而不显示，如果将数字格式更改为常规，则所有时间都将转换为一天的小数。|542146|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

One convenient trick to entering elapsed times into Excel is to have two zeros and a colon before the number of minutes, details follow. For copy and paste operations into Excel without have to worry about formatting at all one can use the format 00:XX:XX where XX are two digits totaling &lt; 60. In that case, Excel will echo 0:XX:XX in the cell contents displayed and store the data as 12:XX:XX AM. If one pastes data in a 00:XXX:XX format into Excel, or 00:XX:XX where either XX > 59 this will be converted into a fraction of a day.

For example, 00:121:12 becomes 0.0841666666666667, which if multiplied by the number of seconds in a day, 86,400, becomes 7272 s. Next, 00:21:12 would by default show 0:21:12 stored as 12:21:12 AM. Finally, 00:21:60 becomes 0.0152777777777778, also a fraction of a day. 

This suggestion is made merely to avoid having to worry about specific formatting in Excel, and letting the program worry about it. Note, for Excel data internally formatted as 12:XX:XX AM one can only use certain Excel commands, for example, one can take an average. However, subtraction will only work when the result is a positive number. Such that converting times into seconds, fractions of a day, or other real number is suggested for access to more complete arithmetic operation coverage. 

For example, if one has a column of mixed time formats, or times that are negative and will not display, if one changes the number formatting to General, all the times will be converted to fractions of a day.

I need to create and Excel table that computes daily training times. each row has the following fields: Date, Distance, Time and Minutes/Km. My main problem is that I want to display the Time in format of <code>mm:ss</code>. For example: 24 min and 3 sec should be <code>24:03</code>, but "Excel" turns it to 00:03:00 (3 min after midnight). I've tried setting up "special formatting" but still "Excel insists" on formatting the hours. More over, training may exceed 60 minutes and I still want it only as <code>mm:ss</code> 
My second question is after I'm done with the formatting issue, what is the simple way to compute Time/Distance?

Excel 2007: How to display mm:ss format not as a DateTime (e.g. 73:07)?

数据存储

我需要创建和Excel表，计算每天的训练时间。每一行都有以下字段:日期、距离、时间和分钟/公里。我的主要问题是我想以mm:ss格式显示时间。例如: 24分3秒应为24:03，但"Excel“将其转换为00:03:00 (午夜后3分钟)。我试过设置“特殊格式”，但"Excel仍然坚持“设置小时的格式。更重要的是，训练可能...

问Excel 2007:如何显示mm:ss格式而不是DateTime (例如73:07)？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel 2007:如何显示mm:ss格式而不是DateTime (例如73:07)？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel 2007:如何显示mm:ss格式而不是DateTime (例如73:07)？
EN