Excel TIME持续时间超过24小时

本文介绍了Excel TIME持续时间超过24小时的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

数据是:

A1 = 29. B1 = 30. C1 = 2. D1 = TIME(A1, B1, C1).

如何让D1返回29:30:02?

将D1单元格格式化为[hh]:mm:ss无效！

如果我一个月内跑了29个小时，Excel会认为我只跑了5个小时.

谢谢.

谢谢！！我得到了它！！！ D1 = TIME(A1, B1, C1) + INT(A1 / 24)

解决方案

您得到的结果正是时间函数起作用:

小时必填.从0(零)到32767的数字代表 小时.任何大于23的值都将除以24，然后 余数将被视为小时值.例如，TIME(27,0,0) = TIME(3,0,0)= .125或3:00 AM.

你可以做

D1

中的

=A1/24+B1/24/60+C1/24/60/60

然后格式为[hh]:mm:ss的结果值将显示29:30:02.

这是因为在Excel中1是1天.所以1/24是1小时，1/24/60是1分钟，1/24/60/60是1秒.

Data is:

A1 = 29. B1 = 30. C1 = 2. D1 = TIME(A1, B1, C1).

How can I get D1 to return 29:30:02?

Formatting the D1 cell to [hh]:mm:ss does NOT work!

If I ran for 29 hours in a month, Excel thinks I ran for only 5 hours.

Thank you.

Edit: Thank you!! I got it!!! D1 = TIME(A1, B1, C1) + INT(A1 / 24)

解决方案

The result you get is exactly how TIME function works:

Hour Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.

You could do

=A1/24+B1/24/60+C1/24/60/60 in D1

Then the resulting value formatted as [hh]:mm:ss will show 29:30:02.

That is because 1 is 1 day in Excel. So 1/24 is 1 hour, 1/24/60 is 1 minute and 1/24/60/60 is 1 second.