将时间段格式化为天数,小时和分钟数 [英] Format a time span as a number of days, hours and minutes
问题描述
这是纯粹的Excel表格问题。
This is purely an Excel sheet question.
我有一个时间跨度,基本上是两个单元格之间的差异,每个单元格包含一个日期:
I have a time span that is basically a difference between two cells, each containing a date:
通过将数字格式更改为 [h]:mm
,我已经设法得到时差数据。现在我想得到几天,几小时和几分钟的差异。我试图将数字格式设置为 [d]:[h]:[mm]
但Excel不接受。
I have managed to get the time difference in number of hours and minutes by changing the number format to [h]:mm
. Now I would like to get the difference in number of days, hours and minutes. I tried to set the number format to [d]:[h]:[mm]
but it is not accepted by Excel.
这是我现在所在的:
A B C (=A2-A1)
---------------- ---------------- ----------
1| 14/10/2011 00:00 17/10/2011 07:50 79:50
2| 14/10/2011 00:00 17/10/2011 11:00 83:00
这是什么我想得到:
A B C (=A2-A1)
---------------- ---------------- ----------
1| 14/10/2011 00:00 17/10/2011 07:50 3:7:50
2| 14/10/2011 00:00 17/10/2011 11:00 3:11:00
我怎样才能做到这一点?有可用的数字格式吗?
How can I do that? Is there a number format available for that?
推荐答案
您可以使用 TEXT
= TEXT(B1-A1,d:h:mm)
注意,直接在单元格上使用简单的数字格式可以实现相同的效果
Note the same effect can be achieved using a simple number format on the cells directly
- 选择您的范围(单元格
C1,C2
等) - 右键单击格式化单元格
- 输入
d:hh:mm
- select your range (cells
C1, C2
etc) - right click and Format Cells
- Custom
- Type
d:hh:mm
如果与您的示例数据不同,您的日期差异超过31天,那么一种方法如
= INT(B1-A1)&:& TEXT (B1-A1,h:mm)
将工作
If unlike your example data, your date differences exceed 31 days, then an approach such as
=INT(B1-A1)&":"&TEXT(B1-A1,"h:mm")
will work
这篇关于将时间段格式化为天数,小时和分钟数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!