如何从文本格式的单元格中获取小时分钟的总和?

如何从文本格式的单元格中获取小时分钟的总和?

我有一列数据如下:

2 Hour, 23 Minute
1 Hour, 10 Minute

我需要以小时分钟格式计算此列的总和。可以是 3 小时 43 分钟。或者 3:43 也可以。

到目前为止,我已成功将文本转换为这种格式->2:23, 1:10

答案1

请执行下列操作:

在此处输入图片描述

怎么运行的:

  • 在单元格 E22 和 E23 中输入 02:33 和 01:10。

  • 在两个单元格上应用此格式:

    [h] "hours ," mm "minutes"

  • 在单元格 E25 和 E26 中输入此公式:

    =E22+E23

  • 应用的格式是 [h] "hours ," mm "minutes"

  • 单元格 E26 的格式为h:mm

根据需要调整公式中的单元格引用。

答案2

数据在A1通过A5, 在B1进入:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1," Hour, ",":")," Minute",":00"))

并向下复制。然后将适当的格式应用于列

在此处输入图片描述

最后,在另一个单元格中输入:

=SUM(B:B)

在此处输入图片描述

答案3

使用 VBA 函数(设置变量:a带列名、j带起始行、k带结束行:

Function Calc()
  Dim c As String
  Dim j As Integer
  Dim k As Integer
  Dim n As Integer
  Dim res As Integer
  c = "A"
  res = 0
  j = 1
  k = 8
  Dim varCell As Variant
  Dim testSplit,testWord
  For i = j To k 
    varCell = Range(c & i).Value
    testSplit = Split(varCell,",")
    For Each o In testSplit
      testWord = Split(o," ")
      Select Case UBound(testWord)
        Case "Second"
          n = testWord(0)/60
          res = res + n
        Case "Minute"
          n = testWord(0)
          res = res + n
        Case "Hour"
          n = testWord(0)*60
          res = res + n
      End Select
    Next
  Next
res = res/60
End Function

res结果如下。

答案4

如果您的格式如您所显示的那样:

<hours><space><text><minutes><space><text>`

如果您拥有 Windows Excel 2010+,则可以使用以下公式:

someCell: =(SUM(FILTERXML("<t><s>" & SUBSTITUTE(TRIM(A1:A6)," ","</s><s>") & "</s></t>","//s[1]"))*60+
SUM(FILTERXML("<t><s>" & SUBSTITUTE(TRIM(A1:A6)," ","</s><s>") & "</s></t>","//s[3]")))/1440

自定义格式 someCell 为[h]:mm

  • 提取小时数并乘以 60
  • 添加分钟
  • 将上述总和除以 1440(一天的分钟数)
  • 将结果格式化为显示小时:分钟

在此处输入图片描述

相关内容