☰

如何更改电子表格中的 hh:mm:ss 格式

tag-icon • tag-icon microsoft-excel spreadsheet microsoft-excel-2010 openoffice-calc
如何更改电子表格中的 hh:mm:ss 格式

目前，我有一个数据集“mm：ss”，需要保持/以这种方式计算。

问题是，Microsoft Excel、Google Spreadsheets 和 OpenCalc 都将“mm:ss”解释为“hh:mm”，更糟糕的是，在我复制到 Excel/GSheets/Calc 的任何内容中添加“:ss”（以“:00”的形式）。

  1. 我发现没有办法删除它，除非先使用“搜索和替换”“：”，然后使用“00”，最后应用自定义格式0:00:00或者0”:“00”:“00。（我也尝试过“=连接“可以工作，但对我来说根本不起作用……）

  2. 此外，使用“0:00:00”实际上只有在我已经有电子表格的情况下才有效。当我将原始数据（来自 MS OneNote）复制到格式化为 0:00:00 的单元格中时，我没有遇到所描述的问题，但复制的数据仍然包含“：”，因为 MS OneNote 没有“搜索和替换”功能。我唯一的想法是采取额外的步骤，将数据复制到记事本并复制回来。

但这两种方法都不是解决办法，因为我的下一步应该是总结那些数字。

这是行不通的，因为任何基数看起来都像“1234”，而不是“12:34”（十二分三十四秒，也就是说不是谈论 12:34 AM/PM（时间）但持续时间！）正如他们应该的那样。

据我了解，为了正确总结会议纪要，我必须使用自定义格式“[时]:分:秒“，但问题是我首先必须将数据从当前的“hh:mm:00”获取到（基本上）“0:mm:ss”。但重要的是要记住，这些实际上不是小时！（即/60 不起作用，因为它会产生错误的数字！）有问题的“hh”实际上是“mm”，但由于 Excel/Calc/GSheets 强制我进行数据解释，而没有任何方式可以更改此设置，所以我别无选择。

我以前从未使用过 VBA，但我愿意尝试一下。

答案1

我编写了一个 VBA 函数来添加您的分钟和秒：

Function addTime(rng As Range) As String
    Dim timeArray As Variant
    Dim sumSec As Integer
    Dim sumMin As Integer
    Dim i As Range

    Dim secStr As String
    Dim minStr As String
    Dim hrStr As String

    sumMin = 0
    sumSec = 0

For Each i In rng
        On Error GoTo Continue
        If i.Value <> "" Then
            timeArray = Split(i.Value, ":")
            sumMin = sumMin + timeArray(0)
            sumSec = sumSec + timeArray(1)
        End If
Continue:
        On Error GoTo 0
    Next i

    secStr = Modulo(sumSec, 60)
    If (secStr < 10) Then
        secStr = "0" & secStr
    End If

    minStr = Modulo((sumMin + sumSec \ 60), 60)
    If (minStr < 10) Then
        minStr = "0" & minStr
    End If

    hrStr = sumMin \ 60

    addTime = hrStr & ":" & minStr & ":" & secStr    
End Function

Function Modulo(a, b)
    Modulo = a - (b * (a \ b))
End Function

将这两个函数放在一个模块中。

要使用我的函数，首先需要将数据原样导入 Excel，即不要让 Excel 将数据更改为日期时间格式。因此：

  1. 首先，点击要粘贴数据的列
  2. 将格式转换为“文本”
  3. 粘贴您的数据
  4. 类型=addTime(A1:A10)（使用“A1:A10”作为 10 个数据点的示例）

答案2

如果我理解正确的话，你的问题是

1) 如何输入 hh:ss 数据而不会让 Excel 弄乱

2）如何将它们相加并得到正确答案

我知道这是一种黑客行为，但如果你使用格式 [h]:mm，然后输入你的数据，它就会起作用。

即使您输入的是分钟和秒，Excel 也会将其作为小时和分钟进行计算，但会以数字形式给出您想要的答案，例如 1:45 + 1:30 = 3:15 和 51:45 + 61:30 = 113:15

答案3

如果您导入mm:ss数据但 Excel 将其视为，hh:mm那么您可以除以 60 以转换为正确的 mm:ss 数据。

要将整个时间值范围除以 60，可以使用此方法：

  1. 在空白单元格中输入 60，然后复制该单元格
  2. 选择 hh:mm 值的范围
  3. 右键单击并从菜单中选择“选择性粘贴”
  4. 在下一个对话框中的“操作”下，选择“除法”>“确定”>“ESC”
  5. 重新格式化为所需的 mm:ss 格式

现在你的值看起来与以前相同，但 Excel 现在根据需要将它们视为 mm:ss

此链接显示一个工作表，其中 A 列包含随机时间值（格式为 [h]:mm），B 列和 C 列中 A 列的值除以 60，但格式不同 - 注意 A 列与 C 列完全相同 - 按 F9 可生成不同的时间值

相关内容