How to change the hh:mm:ss format in spreadsheets
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:
- First, click the column(s) you want to paste your data in
- Convert the Format to "Text"
- Paste your data
- Type
=addTime(A1:A10)
(using "A1:A10" as an example of 10 data points)
Related videos on Youtube
grunwald2.0
Updated on September 18, 2022Comments
-
grunwald2.0 over 1 year
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.
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...)
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.
-
grunwald2.0 about 11 yearsI want to add that I googled around quite a lot, but all solutions that I found were related to simply summing up hh:mm data, which is much easier, if the data base is already (in) hours - inlike mine.
-
grunwald2.0 about 11 yearshh:ss isn't possible as far as I know. Only hh:mm or mm:ss. What do you mean thus?
-
grunwald2.0 about 11 yearsAnd I used [h]:mm, but the problem is it won't work properly, because the data base is false! If excel calculates hh:mm, while the actual numbers represent mm:ss, I will get a wrong solution.
-
gabriel about 11 yearsgrunwald2 ... This is confusing. You start by saying "The problem is, Microsoft Excel, Google Spreadsheets and OpenCalc all interpret "mm:ss" as "hh:mm" ", then I show with examples that the math works the same for hh:mm as mm:ss. Next you make a statement "I will get a wrong solution". Can you elaborate and give an example please?
-
grunwald2.0 about 11 yearsNope, Excel doesn't convert 90:15 to 1:30:15, so how is dividing by 60 gonna help? I tried to describe several times that dividing doesn't help. If I e.g. have the value 30:30(:00) (minutes:ss) and divide it by 60, then the new value is - yes - in minutes 00:50:50, which is more than the original value, i.e. totally wrong.
-
grunwald2.0 about 11 yearsWell, I actually just noticed IT WORKS correctly after I put the measures in place I described above. But not entirely, because the break-off point seems to be "100" not 60, to when a value is moved to the left. i.e. with a short array of numbers it somehow works, but if they get more, the calculation is off, because both minutes and seconds get /100 not /60 by the SUM function.
-
grunwald2.0 about 11 yearsThe problem why your solution doesn't work, that in my case I don't enter the data, MS OneNote does !! So it is automatic, it is already there, i.e. I cannot "prep" the Custom format! -.- And again, I don't want to have to retype all my data, then why do I need excel? Then I'm faster with pen & paper or my manual calculator!!
-
teylyn about 11 years@grunwald2.0 -- Have you tested it? I wrote 90:15 into OneNote, copied it into Excel, where it converted to 90:15:00 (hh:mm:ss). Divided by 60 it returned 1:30:15 (hh:mm:ss) or 90:15 formatted with [mm]:ss. Also, I've never, ever seen barry post anything wrong when it comes to time/date issues.
-
barry houdini about 11 yearsThanks teylyn - I know it works but I tested it before I posted anyway - it certainly worked for me - how do you get 30:30 converted to 00:50:50? If you divide 30 hours by 60 you get half an hour which is 30 minutes, if you divide 30 minutes by 60 you get 30 seconds, so 30:30:00 divided by 60 gives you 00:30:30 as required - what am I missing?
-
barry houdini about 11 yearsI added a link to my answer - it shows random time values divided by 60 - you can see that any xx:yy hours:mins value is converted to an identical looking xx:yy mins/secs value
-
grunwald2.0 about 11 yearsAgain, sadly I have little influence on the mess MS OneNote and Excel produce together, I can only try to wrap my head around of how to manipulate the existing data such as 12:20:00 (hh:mm) towards what I need. Alternatively, I have the "pure" data in OneNote, but the problem is, any spreadsheet software I tried converts my data to hh:mm(:ss) without me having a say concerning this upon import. Am I missing an import option somewhere?
-
grunwald2.0 about 11 yearsThanks Amer, I'll try this out ASAP! Sadly I have already irreversibly converted my data from OneNote to Excel at the moment. But I'll it out with a new dataset.
-
grunwald2.0 about 11 yearsOk, sorry, @barryhoudini it seems to indeed work to also take a sum of those. But I have frankly problems to understand how these random numbers relate to my data? i.e. how do I manipulate my "A" column data row to work equivalent to the file you attached? Again, my times aren't initially in the [h]:mm format, but in the default custom excel format, in my case that was hh:mm, no idea why. My target format is: mm:ss or [h]:mm:ss
-
barry houdini about 11 yearsThey were just examples to show you how the division by 60 would work to convert any hh:mm to mm:ss - I would still suggest that you use the approach I proposed in my answer to convert a whole range of hh:mm data to mm:ss....or you can use a formula in an adjacent column as per my example worksheet