将数值转换为时间(Microsoft Excel)
山姆有很多包含时间的工作表。问题在于时间的格式为“ 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 [将数值转换为时间]。