将现有的Excel单元格格式设置为不带日期的时间格式

Question

我正在使用Excel 2010表格记录事件发生的日期和时间。日期在一列中，并在我键入11-17时，自动格式化为17-Nov (我对此感到满意)。时间在另一列中。

我想找出事件发生的平均时间，而不考虑日期，所以我会使用=AVERAGE(C1:C10)。但是，我只获得一个日期（例如17-APR）。

在输入数据之前，我没有对单元格进行格式设置，我会直接将3:27pm事件输入为1527，并且不会发生重新格式化。

现在，当我尝试将列重新格式化为hhmm时，到目前为止输入的所有数字都变成了0000。当我尝试编辑0000时，它被格式化为6/13/1906 12:00:00 AM。

我想要做的是使时间格式为hhmm，不包括单元格中的日期，并且能够在其上运行公式，例如事件发生的平均时间。

总结：

*当前时间只是简单地输入为####。我将3:27pm输入为1527。

*尝试重新格式化时间列会导致该列中以前有####的所有单元格都变成0000。

*修改0000显示为6/13/1906 12:00:00 AM *我想将时间格式化为hhmm，以便我可以简单地键入2357，并将其显示为2357，但明白我的意思是23:57，并让我取平均值。

*即使能够输入1547并自动格式化为15:47或3:47p也很好。

感谢您阅读我的问题！

- sc_jal

Answer 1

一种应用自动格式的简单方法（尽管Excel不会将其视为真正的“时间”）是进入"单元格格式">"自定义">并使用##":"##。这将把1245转换为12:45。请注意，这将是一个文本字符串，因此如果您将其复制到另一个单元格，然后再应用时间，它将显示为12:00:00。Excel也无法对其运行公式，但这是一种快速而简单的使其看起来漂亮的方法。

另一个选择是使用公式，例如=TIME(LEFT(A1,2),RIGHT(A1,2),)，其中A1将被替换为您实际引用的单元格。这将把数字转换为Excel将识别为时间的时间，从而可以在其上运行其他函数，但需要另一列。

- NicCio

Answer 2

如果您输入的时间是4位数字，您需要进行计算以获取小时和分钟，然后使用TIME函数获取实际时间：

=TIME(A1/100,MOD(A1,100),0)

另一种方法是

=LEFT(A1,2)/24+RIGHT(A1,2)/1440

但是你必须将结果格式化为时间。

如果你将一个数字如1547格式化为日期，Excel会将其视为距离1900年1月1日大约4年的时间，因此它将以英国格式为26/3/1904或美国格式为3/26/1904的形式呈现。

请注意，time函数只能给出最多23:59:59（存储为0.999988426）的值，但第二种方法将给出一个datetime值，其中一个或多个整数表示天数。如果您想对跨越多天的时间进行计算，这可能很有用。

上述行为是因为日期和时间被存储为实数，整数部分表示天数，小数部分表示一天中的时间。尽管微软在这里提供了误导性信息，但实际上日期从1899年12月31日开始（写作0/1/1900），序列号为0，然后每天递增1。

- Tom Sharpe