Excel时间dd/hh/mm转换为分钟

How do I convert turn around time written in an Excel cell as (0D/1H/6), meaning 0 days, 1 hour and 6 minutes to minutes only in another or adjacent Excel cell. I would like to automate that to make the conversion easy. Thanks.

在Excel计算单元中，我如何将时间转换为(0D/1H/6)，即0天，1小时，6分钟，仅在另一个或相邻的Excel单元格中。我想自动化它，使转换变得容易。谢谢。

2 个解决方案

#1

It is possible using some string functions. The code below calculates the minutes, given a cell (A1 in this case) with a string formatted the way you specified:

可以使用一些字符串函数。下面的代码计算时间，给定一个单元格(本例中为A1)，字符串格式化了您指定的方式:

=VALUE(LEFT(A1;FIND("D/";A1)-1))*24*60 + VALUE(MID(A1;FIND("D/";A1)+2;FIND("H/";A1)-FIND("D/";A1)-2))*60 + VALUE(RIGHT(A1;LEN(A1)-FIND("H/";A1)-1))

For instance, in your case "0D/1H/6" gives 66.

例如，在你的例子中“0D/1H/6”表示66。

#2

As I dont think thats a format Excel natively understands I would use a VBA formula for this.

因为我不认为这是一种格式Excel天生理解我将使用VBA公式。

Alt+F11, Insert -> Module & add

Alt+F11，插入->模块，添加

Public Function TOMINS(value As String) As Long
    Dim parts() As String: parts = Split(value, "/")
    If (UBound(parts) = 2) Then
        TOMINS = Val(parts(0)) * 1440
        TOMINS = TOMINS + Val(parts(1)) * 60
        TOMINS = TOMINS + Val(parts(2))
    End If
End Function

Then if the value is in A1 & you want the minutes in B1, make B1 =TOMINS(A1)

如果值在A1中你想要在B1中记录时间，让B1 =TOMINS(A1)

#1