Excel – How to change the hh:mm:ss format in spreadsheets

microsoft excelmicrosoft-excel-2010openoffice-calcspreadsheet

Currently, I have a data set which is "mm:ss" and needs to stay / be calculated that way.

The problem is, Microsoft Excel, Google Spreadsheets and OpenCalc all interpret "mm:ss" as "hh:mm" and to add insult to injury add ":ss" (in form of ":00") to anything I copy into Excel/GSheets/Calc.

  1. I have found no way to remove this, except to use "Search and Replace" first ":" then "00" and then at last apply the custom format 0:00:00 or 0":"00":"00. (I also tried to get "=Concatenate" to work, but that didn't work for me at all…)

  2. Additionally, using the "0:00:00" is in fact only working if I already have a spreadsheet.
    When I copy the original data (from MS OneNote) into a cell formatted with 0:00:00, I don't have the problems described, but the copied data still contains ":", as MS OneNote doesn't have a "Search and Replace" function. My only idea is to take an extra step and copy the data to Notepad and back.

But both approaches are no solution, because my next step should be to sum up those numbers.

Which doesn't work, because any base numbers look like "1234" and not "12:34" (twelve minutes thirty-four seconds, i.e. I am not talking about 12:34 AM/PM (time) but duration!) as they should.

As far as I understood, to sum up minutes properly, I have to use the custom format "[h]:mm:ss", but the problem is that I first have to get the data to (basically) "0:mm:ss" from currently "hh:mm:00". But it is important to remember that those aren't in fact hours! (i.e. /60 doesn't work, because it produces wrong numbers!) The "hh" in question are actually "mm", but because Excel/Calc/GSheets forces their data-interpretation on me without any way of recource of changing this setting, I am out of options.

I never used VBA before, but I am willing to try it.

Best Answer

I've written a VBA function that will add your minutes and seconds:

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

Put the two functions in a module.

To use my function, first you need to get your data into Excel as-is, i.e. not let Excel change the data into its date-time format. So:

  1. First, click the column(s) you want to paste your data in
  2. Convert the Format to "Text"
  3. Paste your data
  4. Type =addTime(A1:A10) (using "A1:A10" as an example of 10 data points)
Related Question