代码之家 › 专栏 › 技术社区 › Jyrki Lahtonen

控制Excel时间格式输入/输出

time-format excel

Jyrki Lahtonen · 技术社区 · 7 年前

问题: mm:ss h:mm:ss . Excel似乎没有内置格式,其中小时字段是可选的。理想情况下,为了优化我的工作流程,我希望有一个单元格格式,以便输入 47:12 被解释为47分12秒 1:09:38 应解释为1小时9分38秒。然而,Excel是我找到的最合适的单元格格式,它将坚持输入 47:12 1:03:00 如果我的意思是秒字段的值为零。

如何让Excel认识到,当格式最多可以处理三个数字作为输入时,当只给定两个数字时,它会将它们移向 ?

思考: 0:47:12 好吧但由于大多数时间都不到1小时,这在一定程度上是浪费精力。此外,使用这种格式,数据与多余的数据一起显示在屏幕上 0: . 更糟糕的是(IIRC)那些前导零也出现在印刷版本中,这对于初级参与者来说是奇怪的(甚至是侮辱性的)。

我的黑客: mm,ss

3 回复 | 直到 6 年前

IQV 7 年前

我设法创建了一种格式,其中小时字段是

如果cellvalue>0.04166667格式hh:mm:ss

结果:
47:12
01:09:38
01:00:00

所以你得到了你真正想要的,你可以使用原始值进行排序等等。

对于输入 您需要另外四列。您可以根据需要输入时间,例如47:12和1:09:38。在接下来的三列中,您将这些值分为小时、分钟和秒,其中解释限制为3小时(03:00),即0.125。

以下是拆分列的公式(您的输入在B1中):

小时: =IF(B1>0.125,0,HOUR(B1))
=IF(B1>0.125,INT(B1)*24+HOUR(B1),MINUTE(B1))
秒数: =IF(B1>0.125,MINUTE(B1),SECOND(B1))

=TIME(C1,D1,E1)

Ron Rosenfeld 7 年前

如果您将以

`mmm,ss`

如果逗号是小数点,则可以使用以下简单公式将其转换为“Excel时间”:

=DOLLARDE(A1,60)/1440

根据需要格式化结果。

h:mm:ss

h 要仅以60分钟或更大的值显示,请使用

[<0.0416666666666667]mm:ss;h:mm:ss

请注意,输入秒时必须始终使用两位数字。换句话说

6,2   will translate to 6 min 20 sec.
6,02  will translate to 6 min  2 sec

Community Nick Dandoulakis 4 年前

我真的很喜欢上面IQV的回答,但正如评论部分所指出的,数据输入端需要前导零。如果出于任何原因,这是不可接受的,您可以使用以下难看的公式将以mm,ss的常用方法输入的时间转换为hh:mm:ss,并根据需要显示hh:。不幸的是,它将整个内容转换为文本,这意味着您无法再对其执行数学运算。

=IF(FIND(".",MOD(D2,60)&".")=2,"0","")&MOD(D2,60)

由于使用小数分隔符,公式将变为:

IF(FIND(",",MOD(D2,60)&",")=2,"0","")&MOD(D2,60)

IF(FIND(",";MOD(D2;60)&",")=2;"0";"")&MOD(D2;60)

可能有一些更简洁的公式,但这会让你开始。只需将D2替换为存储时间的位置。

假设您将单元格D6中的数据存储方法更改为hhmm,ss。您可以拆分信息并将其重新组装到一个显示友好的版本中,如下所示。

=IF(FIND(".",D6)<=3,LEFT(D6,2)&":"&RIGHT(D6,LEN(D6)-FIND(".",D6)),LEFT(D6,FIND(".",D6)-3)&":"&MID(D6,FIND(".",D6)-2,2)&":"&RIGHT(D6,LEN(D6)-FIND(".",D6)))

您需要将列表分隔符替换为,然后将coma替换为小数。