在 Excel 中,给定一个代表一定时间量的数字(例如 25.5 小时),有没有办法将其格式化以使其显示为 1d 1h 30m?
赏金问题:
有没有办法将月份添加到此格式?例如 1M 1d 1h 1m。当前格式的问题是它无法处理超过 32 天的任何内容(即 32.5 被格式化为 1d 8h 0m)
答案1
您必须将这些小时数转换为 Excel 可以理解的内容。
假设您的小时数为十进制形式(例如 25.5 小时、37.25 小时),您可以这样做:
步骤1:
将数量除以 24。
这样就可以得到小时与天的比率,或者将数量转换为天的分数(例如,25 小时转换为 1 1/24 或 1.04)。
第2步:
将包含公式或转换值的单元格的数字格式更改为风俗
(按Ctrl+ 1> 自定义)。使用此格式字符串:
[<=0.0416551] [m]"m";[<1]h"h" m"m";d"d" h"h" m"m"
0.0416551 = 十进制的 0:59:59
或者
[<1]h"h" m"m"; d"d" h"h" m"m"
或者
d"d" h"h" m"m"
例子:
答案2
如果时间以小时为单位,那么这是一种方法:
=TRUNC(A1/24) & "d " & TRUNC(A1-TRUNC(A1/24)*24) & "h " &
60*((A1-TRUNC(A1/24)*24)-TRUNC((A1-TRUNC(A1/24)*24))) & "m"
答案3
(由于我的声望值不足 50,因此无法添加评论)
要显示月份,您可以使用以下格式(请注意,这并不能直接满足您的确切需求,但您可以根据需要进行修改)。我还在字符串中添加了一些颜色以明确时间范围:在上面的示例中,红色表示超过一天,黑色表示一天之内。第二个示例中,红色表示一年以上,蓝绿色表示一个季度到一年之间,其他情况为蓝色。当然,您可以根据需要使用不同的格式规范。
[Red][>1]yy\y mm\m dd\d hh:mm:ss;[Black][<=1]hh:mm:ss;[Red]-0.000000;[White]@
或者
[Red][>=364.5]yy\y mm\m dd\d;[Color50][<=122]mm\m dd\d;[Color5]mm\m dd\d;[Black]@
要将“32.5”格式化为 32d 12:00:00,您可以使用[d]\d hh:mm:ss
,注意括号,它表示您希望在天数中包含大于 31 的值。
\ 符号指示 excel 明确包含 \ 符号后面的字母,而不是将其解释为命令的元数据(例如,没有斜杠的 d 将立即再次输出天数)。这相当于您在其他示例中看到的“ ”符号。
一些限制和/或错误:
1) 您不能指定超过 4 种格式类型,其中最后一种必须是纯文本。这意味着只有 3 种可用于格式化日期,其中第 3 种不能包含任何大于/小于比较。
2) 计算日期差值时,月份 mm 实际上高 1(截至 Excel 2010),因为 Excel 实际上将日期差值存储为完整日期时间,并且没有第 0 个月,而 1 月是第一个月。天数似乎没有这个问题,因为“1.1”显示为 1 天,而 0.9 将显示为 0 天(两者都是 1 个月:P)。我不知道如何在单元格样式对话框中进行数学运算(例如 (mm-1)\m),因此如果您真的希望这些数字准确(从月份中减去 1),您必须编写一个 vB 脚本来转换它并显示最终的文本字符串。
3)Excel 不能正确处理负时间差
或者,你可以使用类似以下公式在单元格中设置格式
=IF((B2-A2)>1,DATEDIF(A2,B2,"y")&"y "&TEXT(DATEDIF(A2,B2,"ym"),"00")&"m "&TEXT(DATEDIF(A2,B2,"md"),"00")&"d",TEXT(A2-B2,"hh:mm:ss"))
如果小于一天,则信息将显示为 hh:mm:ss;如果大于一天,则信息将显示为 y MM dd。这不会像上面那样出现一个月的误差。但是,这个单元格实际上是文本,不再是数值。所以你不能(直接)用它做任何进一步的数学运算。