Excel中的时间跨度格式 [英] Timespan formatting in Excel

问题描述

我当前使用的是自定义格式 m:ss.00 ，它在超过一分钟的任何时间(例如， 1:02.47 .但是，如果我输入的时间少于一分钟，例如 57.66 ，它将转换为 50:24.00 .我可以通过输入 0:57.66 来解决此问题，但我宁可不必总是键入前导 0:，而我不希望前导0:仍然显示.我希望它能理解，如果我跳过数字后跟冒号部分，可以将其解释为没有分钟，而只需显示 57.66 .

I'm currently using the custom format, m:ss.00, and it works perfectly for any times that exceed one minute, e.g. 1:02.47. However, if I enter a time less than a minute, e.g. 57.66, it gets transformed into 50:24.00. I can get around this by entering 0:57.66, but I'd rather not have to always type the leading 0: and I don't want the leading 0: displayed anyway. I'd like for it to understand that if I skip the number followed by a colon part, to interpret this as no minutes and simply display 57.66.

推荐答案

在Excel日期时间:

In Excel datetimes:

1   =   1 day

...因此:

1 / 24            =  1 hour    =  0.04166667  formatted as m:ss.00 is 0:00.00

1 / 24 / 60       =  1 minute  =  0.00069444  formatted as m:ss.00 is 1:00.00

1 / 24 / 60 / 60  =  1 second  =  0.00001157  formatted as m:ss.00 is 0:01.00

因此，如果您有秒数，例如 50.24 ，则可以将其除以 x/24/60/60 与 x/86400 相同.

So if you have a number of seconds like 50.24, you can convert it to Excel DateTime by dividing it by x/24/60/60 which is the same as x/86400.

这会将其转换为秒.

如果方法超过一分钟，则该方法起作用的原因基本上是通过消除过程来完成的.像 50.24 这样的实数"看起来就像一个数字，因为它没有:冒号或其他任何将其区分为日期时间的内容.输入intead 0:50.24 将强制Excel将其识别为日期时间.

The reason your method works if it's more than a minute is basically through the process of elimination. A "real number" like 50.24 just looks like a number since it has no : colon or anything else to distinguish it as a datetime. Entering intead 0:50.24 will force Excel to recognize it as a datetime.

类似地，输入以下日期:

Similarly, entering these dates:

12/01/1999
01/12/1999

...可能有不同的结果( 1999年1月12日或 1999年12月1日)，具体取决于您的使用 M/D/Y 或 D/M/Y )，但是，请输入以下任意一项:

...could have varying results (January 12, 1999 or December 1, 1999) depending on your regional settings (whether your country uses M/D/Y or D/M/Y), however, entering either of these:

01/13/1999
13/01/1999

...将两者都解释为"1999年1月1日"，因为Excel能够消除其他可能性(因为没有第13个月).

...will both be interpreted as "January 1, 1999" since Excel was able to eliminate other possibilities (since there's no 13th month).

• Office.com: Office.com:

  Office.com : Change the Windows Regional Settings

  维基百科: 国家/地区的日期格式

  Wikipedia : Date format by Country

  Office.com:

  Office.com : Add or Subtract Time

  这篇关于Excel中的时间跨度格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！