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?