13问答网 > 如何在Excel里面把时间转换成分钟，如5天11时55分52秒转换成7916分钟，30秒以上算1分，不足30秒则不计入

如何在Excel里面把时间转换成分钟，如5天11时55分52秒转换成7916分钟，30秒以上算1分，不足30秒则不计入

2025-04-03 11:05:55

推荐回答（2个）

回答1：

假设这个数据在A1,B1输入公式如下
=LEFT(A1,FIND("天",A1)-1)*1440+TEXT(TIME(HOUR(RIGHT(A1,LEN(A1)-FIND("天",A1))),MINUTE(RIGHT(A1,LEN(A1)-FIND("天",A1))),IF(SECOND(RIGHT(A1,LEN(A1)-FIND("天",A1)))<30,0,60)),"[M]")&"分钟"

或

=LEFT(A1,FIND("天",A1)-1)*1440+TEXT(RIGHT(A1,LEN(A1)-FIND("天",A1)),"[M]")+IF(SECOND(RIGHT(A1,LEN(A1)-FIND("天",A1)))<30,0,1)&"分钟"

回答2：

自定义函数 = MyMinute(A1)
Function MyMinute(ByVal dt As String) As Integer
MyMinute = 0
If dt = "" Then End
Dim dw(4) As String
Dim xx(4) As Single
Dim tm
dw(0) = "天"
dw(1) = "时"
dw(2) = "分"
dw(3) = "秒"
xx(0) = 24 * 60
xx(1) = 60
xx(2) = 1
xx(3) = 1 / 60
For i = 0 To 3
ps = InStr(dt, dw(i))
If ps >= 0 Then
tm = Left(dt, ps - 1)
tm = Round(tm * xx(i), 0)
dt = Mid(dt, ps + 1, Len(dt) - ps)
MyMinute = MyMinute + tm
End If
Next
End Function