Excel中的时间(24小时以上) [英] Timespan in Excel (above 24 hours)
问题描述
有没有办法在Excel中表示一个TimeSpan(或其他类型的时间),时间可以是24小时以上?那是上午1点(凌晨25点)(公共交通调度中的通用代表)。
它不能只是纯文本,因为我想要对他们进行计算(例如平均值,标准偏差)或图表。
已经是这样,如果你把一个日期或进入单元格的时间,您将获得一个日期时间值。
例如,键入 14:00
到 A1
和 A2
,并设置 A3
到 = A1 + A2
。为什么会显示 04:00
,您可能会想。如果您将单元格格式格式设置为显示日期和时间的答案,例如 dd / mm / yyyy hh:mm
。执行 A1
和 A2
,我们看到:
输入纯时间值的
- 将输入的时间部分存储为日期时间,1900年1月0日的日期部分在数据时间上进行数学运算是一旦你知道零对应于1900年1月0日的午夜,而一个单位= 1天
所以要做你想要的 - 在一个单元格中存储25个小时,让用户和Excel都理解为25个小时 - 只要把一个值 25/24
,以及包含魔术格式代码 [h]
, []
这里表示'允许显示值超过24'。
Is there a way to represent a TimeSpan (or some other type of time) in Excel where hours can be 24 and above?
For example, have a time that would be 25:00:00 for 1 AM (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.
It's already the case that if you put a date or a time into a cell, you get a datetime value.
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:
- 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
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屋!