山姆有很多包含时间的工作表。问题在于时间的格式为“ 1300”,而不是“ 13:00”。因此,Excel将它们视为常规数值,而不是将它们识别为时间。 Sam希望将它们转换为实际时间值。

您可以通过几种方法来完成此任务。一种方法是使用TIME函数将值转换为时间,如下所示:

=TIME(LEFT(A1,2),RIGHT(A1,2),)

此公式假定单元格A1中的时间将始终包含四个数字。如果不是(例如,可能是427而不是0427),则需要对公式进行一些修改:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

该公式基本上提取最左边的一个或多个数字,并将它们用于TIME函数的hours参数,然后将两个最右边的数字用于minutes参数。 TIME返回实际时间值,其格式在单元格中是这样的。

可以使用TIMEVALUE函数采用类似的公式化方法:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

此公式使用REPLACE在适当的位置插入一个冒号,然后TIMEVALUE将结果转换为时间值。您将需要格式化结果单元格,以便它根据需要显示时间。

公式化方法的另一个变体是以这种方式使用TEXT函数:

=--TEXT(A1,"00\:00")

这将返回一个实际时间值,然后您将需要对其进行正确格式化以将其显示为时间。

另一种方法是简单地对原始时间进行数学运算,以将其转换为Excel使用的时间值。一旦意识到时间值只不过是一天的一小部分,这将很容易。因此,时间值是0到1之间的数字,方法是将小时除以24(一天中的小时),将分钟除以1440(一天中的分钟)。这是执行此操作的公式:

=INT(A1/100)/24+MOD(A1,100)/1440

这将确定原始值的小时部分,然后将其除以24。然后将分钟部分(从原始值剩余的部分)除以1440,并将其添加到第一部分中。然后,您可以将结果格式化为时间,并且效果很好。

到目前为止,所描述的所有公式都使用一个新列来进行转换。这很方便,但是您可能希望实际上就地转换该值,而不需要公式。这是宏可以派上用场的地方。以下宏会将您选择的任何单元格转换为时间值,并适当地设置单元格的格式:

Sub NumberToTime()

Dim rCell As Range     Dim iHours As Integer     Dim iMins As Integer

For Each rCell In Selection         If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then             iHours = rCell.Value \ 100             iMins = rCell.Value Mod 100             rCell.Value = (iHours + iMins / 60) / 24             rCell.NumberFormat = "h:mm AM/PM"

End If     Next End Sub

宏使用整数除法来确定小时数(iHours),然后将余数填充到iMins中。然后将其调整为时间值,然后放回单元格中,然后将其格式化为时间。如果需要,可以将单元格格式更改为Excel支持的任何其他时间格式。

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(10101)适用于Microsoft Excel 2007、2010、2013和2016。您可以在此处为Excel的较旧菜单界面找到此技巧的版本:

链接:/ excel-Converting_Numeric_Values_to_Times [将数值转换为时间]。