Excel中24小时以上的时间表示 [英] Time representation in excel above 24 hours
问题描述
有没有一种方法可以表示Excel中的时间值,其中小时可以是24小时或24小时以上?
Is there a way to represent a time value in Excel where hours can be 24 and above?
例如,第二天凌晨1点的时间为25:00:00(公共交通调度中的常见表示方式).
For example, have a time that would be 25:00:00 for 1 AM on the following day (common representation in mass transit scheduling).
它不能只是纯文本,因为我希望能够对它们执行计算(例如平均值,标准偏差)或绘制图表.
It can't just be plain text, because I want to be able to perform calculations on them (averages, standard deviation for example) or chart them.
推荐答案
已经存在的情况是,如果将日期或时间放入单元格,则会获得datetime值.
It's already the case that if you put a date or a time into a cell, you get a datetime value.
例如,在A1
和A2
中键入14:00
,然后将A3
设置为=A1+A2
.您可能想知道为什么它显示04:00
.如果您将单元格 format 设置为显示日期和时间的内容(例如dd/mm/yyyy hh:mm
),就会出现答案.同样对A1
和A2
执行此操作,我们将看到:
For example, type 14:00
into A1
and A2
, and set A3
to =A1+A2
. Why does it display 04:00
, you might wonder. The answer comes if you set the cell format to something that displays date and time, eg dd/mm/yyyy hh:mm
. Do this to A1
and A2
as well and we see:
-
输入的
- 纯时间值将作为日期时间与输入的时间部分一起存储,日期部分为1900年1月0日
- 可以在日期时间进行数学运算,并且一旦您知道零对应于1900年1月0日午夜,并且一个单位= 1天,就可以理解该数字了.
- entered pure time values are stored as a datetime with the entered time portion, and a date portion of January 0, 1900
- doing math on datetimes is possible and becomes comprehensible once you know that zero corresponds to midnight on January 0, 1900, and that one unit = 1 day
因此,您想做的就是-在一个单元格中存储25小时,并让用户和Excel都理解为25小时-只需输入25/24
的值,然后输入包含魔术格式代码的自定义格式[h]
,这里的[]
意思是允许显示的值超过24".
So to do what you want - to store 25 hours in a cell and have it understood by both users and Excel to mean 25 hours - just put a value of 25/24
, and a custom format including the magic format code [h]
, the []
here meaning 'allow the displayed value to go over 24'.
这篇关于Excel中24小时以上的时间表示的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!