1. Computing

Sum an "Hours and Minutes" Time Column in Excel

By January 3, 2010

Follow me on:

I have a business relationship with someone who sends me an Excel spreadsheet with a column of time amounts formatted as "hours and minutes" like this ...

15 min
1 hr
1 hr 15 min
1 hr

... plus a total at the bottom. I wanted to check the total, but I couldn't find any way to get Excel to sum a column of time values like that. So I wrote a program. Here's what I came up with:

Public Sub cnvtTime()
        Dim theTimeRange As Range
        Set theTimeRange = _
            Worksheets( _
            "Sheet1").Range("H13:H69")
        theTimeRange.Select
    For Each rw In Selection.Rows
        theHr = 0
        theMin = 0
        If InStr(1, rw, "hr") Then _
            theHr = Left(rw, 1)
        If InStr(1, rw, "min") _
            Then theMin = _
            Right( _
            Left(rw, Len(rw) - 4), 2)
        Worksheets("Sheet1").Range( _
            "L" & rw.Row).Value = _
            (theHr * 60) + theMin
    Next rw
End Sub

This program is a bit brittle. For one thing, it depends on exact spacing and consistent spelling of "hr" and "min". For another, I have to input the starting range manually into the program source. But, Hey!, it was quick and dirty and it works. The program simply creates a new column with the time values in minutes instead. I can sum that in the spreadsheet.

1 - Does anybody know a way to sum a column formatted as "hours and minutes" directly?

2 - Does anybody know a better way to write this code?

Comments
January 4, 2010 at 2:07 pm
(1) bruce says:

t would be better if the spreadsheet maker would use a standard time format. But they do not like to be easy.

January 4, 2010 at 6:53 pm
(2) saae essa says:

Explain the simple, clear and benefited from many Thanks for these efforts

January 6, 2010 at 1:29 am
(3) Lennie says:

Would have been better if it was in standard time format. You can then use the normal MS Excel functions for calculations. The format of your cell is actually text.

January 6, 2010 at 10:37 am
(4) visualbasic says:

That’s true. It would have been better formatted as DateTime. But I didn’t produce the spreadsheet. I only received it. Sometimes we have requirements like this that we need to work with.

January 6, 2010 at 11:34 pm
(5) TheZip says:

Given that the stated problem is the time formatted as a string with “hr” and “min” as string names to denote the hours and minutes then it “is what it is”. This is not an uncommon type of problem to handed in Excel.

My approach is create four inline functions, getMinutes(‘Cell Index’), getHours(Cell Index), getTotalHours(‘Cell Index’), getTotalMinutes(‘Cell Index). Where ‘Cell Index’ is a selected cell with a time formatted per the requirements. This allows for the processed data to be manipulated in the worksheet as desired as they would return numerical values.

getMinutes would parse the string with the ONLY assumption being that if there are minutes in the string it will be formatted as “min”. No assumption about spaces between numbers and subStrings within the full string. If it finds “min” it attempts to parse out the actual numerical value of the minutes and return the numerical value to the call from where the ‘=getMinutes(‘Cell Range’)’ was called. With the numerical value of minutes displayed these values could be summed. Errors would be returned as #VALUE

getHours would work just like getMinutes but by parsing out the numerical value of the hours if it finds the substring “hr” in the full string.

getTotalMinutes would work similar to getMinutes and getHours but would essentially sum the minutes and hours*60 to return the total time of the string in minutes. It would actually use the work done by getHours and getMinutes by call those functions internally so as not to duplicate code. Again the returned value to the cell could be manipulated in the worksheet as desired.

getTotalHours would work similar to getMinutes and getHours but would essentially sum the hours and minutes/60 to return the total time of the string in hours. It would actually use the work done by getHours and getMinutes by call those functions internally so as not to duplicate code. Again the returned value to the cell could be manipulated in the worksheet as desired.

January 7, 2010 at 1:08 am
(6) visualbasic says:

You’re absolutely right. That is a more “robust” way to do it. (Hey! I said mine was quick and dirty!) My main goal in asking was to see if someone knew some magic thing I had never heard about.

Given that I couldn’t find some magic in Excel that would just do it, presto chango … and I was doing it for a client so it had to work … I agree that your methodology would be better.

January 7, 2010 at 11:28 am
(7) crogers says:

why don’t you just write an if/then that converts to the data form you need and create a macro. that way you can just hit one key and convert and total all at the same time?

January 7, 2010 at 2:56 pm
(8) visualbasic says:

That is the way this is done. The only question is whether there is a clever coding trick that would make the macro better in some way.

January 10, 2010 at 7:45 am
(9) Roski says:

How about you remove all characters besides letters and numbers and spaces? Then the hours would be the first number, and the minutes would be the second number. Then simply multiply the first number by 60 to get minutes, and add to the other ones.

January 10, 2010 at 11:05 am
(10) visualbasic says:

That’s a good idea, and I didn’t think of it before.

But an idea is only as good as the code that you can write with it, so I worked on it for a few minutes to see what could be done.

Replacing the spaces is easy. just add this statement as the first one in the loop:

result = rw.Replace(” “, “”)

But then you still have a problem in that some of the rows will have hours and no minutes; some will have minutes and no hours; and some will have both:

45min
1hr15 min
2hr

So you STILL need some conditional logic (which would look just about like the logic I have in my code) to test for the characters “hr” and “min” and if those are mispelled, you STILL get the wrong answer.

But your idea is an improvement! Extraneous spaces would not be a problem anymore. Thanks for suggesting it.

January 10, 2010 at 4:20 pm
(11) Tom Garth says:

I hate spreadsheets and prefer to use code whenever I have a choice however if you are going to send this spreadsheet to anyone it will go more easily without macros.

So with 3 additional columns you can do it with just Excel.
Copy your 4 values to column A in a new sheet and delete the empty rows between them. Then use the following formulas (written for A here).
C2 = ‘=SEARCH(“H”,A1)’
D2 = ‘=SEARCH(“M”,A1)’
B2 = ‘
=IF(ISNUMBER(C1),
IF(ISNUMBER(D1),
LEFT(A1, SEARCH(“H”, A1)-1)*60
+
LEFT(REPLACE(A1, 1, SEARCH(“H”,A1)+2,”"), SEARCH(“M”,REPLACE(A1, 1, SEARCH(“H”,A1)+2,”"))-1),
LEFT(A1, SEARCH(“H”, A1)-1)*60),
IF(ISNUMBER(D1),
LEFT(A1, SEARCH(“M”, A1)-1),
0))

Copy and paste to rows 2 through 4.

I’m sure there are more efficient methods, but like I said, “I HATE SPREADSHEETS”.

Thanks for the my team didn’t make the playoffs Sunday afternoon diversion.

January 10, 2010 at 4:39 pm
(12) Tom Garth says:

And of course, I listed the cell references wrong. Start with C1, D1, and B1.

January 11, 2010 at 10:22 am
(13) visualbasic says:

I got that. (The very minor error in identifying the cells) Compared to the formula in column B, it wasn’t all that hard to figure out.

You hate spreadsheets, huh!? If I had debugged that formula, I would have hated them too … For a while, at least. You really get mean and nasty when your team loses!

There are three other problems that need to be cleared up before your formulas will actually work that I think I ought to point out for the benefit of anyone who might try to copy them out and verify them. (When these things are fixed, Tom’s formulas really do work.)

1 – This isn’t Tom’s fault, but when the text is posted in the message board, double quotes are converted to an incompatible character value for Excel formulas. You can clear this up by copying Tom’s formulas into Notepad and then simply replacing the incompatible quote with the one used in Excel. (Copy the one in Excel and paste into the Notepad Replace value.)

2 – This isn’t Tom’s fault either, but there are newline characters in the formula as it has been formatted in the message board. It has to be one long string to paste into the Excel formula box.

3 – M-a-y-b-e Tom might have anticipated this, but in some cases, his formula yields a character value instead of a numeric value in the result of the B column. Then a Sum function won’t work. To fix this, mutiply the entire value in Tom’s B column by 1. Then it will autosum.

Here’s what Tom did:

C and D formulas simply tell him whether there is an hour or a minute alpha in the row value. This is nice because it’s not case sensitive and the only thing it depends on is the characters h and m. The column B formula parses out the correct values and multiplies them. That’s why the formula is so long. It’s really three formulas – one for hours, one for minutes and one for both.

If you wanted to, you could do the same trick Tom did in breaking out subformulas from the column B formula. In other words, you could make column B read:

=IF(E1,F1,G1)

where the parts of that monster in column B is in the other three cells. And then you could break the bigger parts of that, and so forth. That way, at least, you would have a debuggable formula because you could check the values of the parts. In fact, Tom’s formula could be simplified by using the values in column C and D in his Column B formula:

=IF(ISNUMBER(C1),IF(ISNUMBER(D1),LEFT(A1, C1-1)*60+LEFT(REPLACE(A1, 1, C1+2,”"), SEARCH(“M”,REPLACE(A1, 1,C1+2,”"))-1),LEFT(A1, C1-1)*60),IF(ISNUMBER(D1),LEFT(A1, D1-1),0))*1

(Tom would never have been able to do it if he hadn’t been consumed with rage by the loss of his team.)

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>
  1. About.com
  2. Computing
  3. Visual Basic

©2014 About.com. All rights reserved.