将时间跨度格式化为天数、小时数和分钟数 [英] 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
如果与您的示例数据不同,您的日期差异超过 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屋!