Hi Larry
I knew befo give him a small finger and he wants the whole hand :-)

Use the following code (This will use the format D:H:H):

Function sum_pseudo_time(rng As Range, Optional color_index As Integer)
As String
Dim cell As Range
Dim ret_str As String
Dim ret_value
Dim time_str As String

ret_value = 0
For Each cell In rng
If color_index Then
If cell.Value < "" And cell.Interior.ColorIndex = color_index
Then
time_str = Replace(Replace(cell.Value, "H", ""), "M", "")
ret_value = ret_value + CDate(time_str)
End If
Else
If cell.Value < "" Then
time_str = Replace(Replace(Replace(cell.Value, "H", ""),
"M", ""), _
"D", "")
ret_value = ret_value + CDate(time_str)
End If
End If
Next
With Application.WorksheetFunction
ret_str = .RoundDown(ret_value, 0) & "D:" & _
.RoundDown((ret_value - .RoundDown(ret_value, 0)) * 24, 0) & _
"H:" & CInt((ret_value * 24 - .RoundDown(ret_value * 24, 0)) * 60) &
"M"
End With
sum_pseudo_time = ret_str
End Function


--
Regards
Frank Kabel
Frankfurt, Germany

Frank the code works really good. thanks so much!

I'm just curious if you can deal with a small x-tra challenge...

suppose I have the following column...

Column D

1D:20H:3M
20H:3M
0H:3M

Adding these should yield... 2D:16H:9M

how can the code you provided be tweaked to accomodate this example?

If you can solve this you're awesome
If you can't it's ok... the world moves on :) , you've helped me a
great deal already.

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/