如何将大于当月天数的持续时间（以天为单位）相加？

发布于 2024-10-01 07:32:31 字数 311 浏览 7 评论 0原文

我在“hh:mm:ss”中得到了很多持续时间，并希望将这些持续时间的总和显示为“dd hh:mm:ss”。只要总天数小于或等于当前月份的天数，Excel 2007 就会正确执行此操作。

不可能像使用小时、分钟和秒那样使用“[dd]”来超载天数。

持续时间示例

A1 00:54:12
A2 14:02:00
A3 12:20:01
A4 23:59:59
A5 =Sum(A1:A4)

我可以使用哪些非数组公式来实现所需的格式？

原文

I got many time durations in "hh:mm:ss" and want to display the sum of these as "dd hh:mm:ss". Excel 2007 does this correct as long the summation number of days is less or equal the number of days in current month.

It is not possible to overload days using "[dd]" as you can with hours, minutes and seconds.

Duration examples

A1 00:54:12
A2 14:02:00
A3 12:20:01
A4 23:59:59
A5 =Sum(A1:A4)

Which non-array formula can I use to achieve wanted format ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往日情怀 2024-10-08 07:32:31

使用公式

=floor(sum(A1:A4))&" "&floor(mod(sum(A1:A4),1)*24)&":"&floor(mod(mod(sum(A1:A4),1)*24,1)*60)&":"&mod(mod(mod(sum(A1:A4),1)*24,1)*60,1)*60

将结果生成为包含天小时：分钟：秒的字符串。
要更正秒的小数部分，并确保每个小时/分钟/秒都有两位数，您应该像这样填充它们：TEXT(formula_section,"00")

最终公式将是：

=floor(sum(A1:A4))&" "&TEXT(floor(mod(sum(A1:A4),1)*24),"00")&":"&TEXT(floor(mod(mod(sum(A1:A4),1)*24,1)*60),"00")&":"&TEXT(mod(mod(mod(sum(A1:A4),1)*24,1)*60,1)*60,"00")

Use the formula

=floor(sum(A1:A4))&" "&floor(mod(sum(A1:A4),1)*24)&":"&floor(mod(mod(sum(A1:A4),1)*24,1)*60)&":"&mod(mod(mod(sum(A1:A4),1)*24,1)*60,1)*60

which will generate the result as a string with the days hours:minutes:seconds.
To correct the fractional seconds, and to ensure each of hours/minutes/seconds get two digits, you should pad them like this: TEXT(formula_section,"00")

The final formula would then be:

=floor(sum(A1:A4))&" "&TEXT(floor(mod(sum(A1:A4),1)*24),"00")&":"&TEXT(floor(mod(mod(sum(A1:A4),1)*24,1)*60),"00")&":"&TEXT(mod(mod(mod(sum(A1:A4),1)*24,1)*60,1)*60,"00")

回复收藏 0 原文

~没有更多了~