change function to loop thru range.

[sedgely]

I have the following function thats takes a cell value in the format 0700-1900 and calculates the number of hours elapsed. It works fine but I would like to be able to use a range of cells as the parameter and work through the cells to get the total hours. Can someone point me in the right direction.

Public Function totalHours(t1 As String)
'
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.
'
Dim tt1 As Double
Dim time1 As Double
Dim time2 As Double
If IsNumeric(Left(t1, 4)) Then

time1 = Left(t1, 4)
time2 = Right(t1, 4)

If time1 < time2 Then
tt1 = (Int(time2 / 100) + (((time2 / 100) - Int(time2 / 100)) / 0.6)) _
    - (Int(time1 / 100) + (((time1 / 100) - Int(time1 / 100)) / 0.6))
Else: tt1 = ((Int(time2 / 100) + (((time2 / 100) - Int(time2 / 100)) / 0.6)) + 24) _
    - (Int(time1 / 100) + (((time1 / 100) - Int(time1 / 100)) / 0.6))
End If

tt1 = tt1 / 24
totalHours = tt1
Else: totalHours = 0
End If
End Function

Cheers, Craig
Si fractum non sit, noli id reficere

 

[nickdel]

Craig, sorry but I'm not quite sure what you are asking for here. Are you saying you have a range of text fields that you want to pull the values from?

and

Si fractum non sit, noli id reficere

If it's not broke then it simply doesn't have enough features!

Nick

 

[sedgely]

Nick
on the sheet there is a range of cells with text values in the format 0800-1900,2100-0700 etc also some may have other text in e.g. "rest day"
the code i posted converts each part into a time and calculates the difference in hours. This works great for a single cell e.g. "=totalHours(c3)" however i want to be able to use e.g. "=totalHours(c3:h3")" so the function will look at each cell in the range perform the conversion and return the sum of hours. Does this make sense?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[Kimed]

And why not do it like this?

Public Function TotalTotalHours(tt as Range)
Dim n as Double
Dim i as Integer
n=0

For i=1 to tt.Rows
  n=n+TotalHours.Cells(i,1)
Next

TotalTotalHours=n
End Function

(or do it through columns instead of rows, or both)

 

[Kimed]

Damn, can't edit a typo - it should be

n=n+tt.Cells(i,1)

 

[Kimed]

Argh!

*This* is what I mean

n=n+TotalHours(tt.Cells(i,1))

 

[sedgely]

Nick
tried your suggestion, just get a "#value" error. this is what i've got up to now

Public Function totalHours(t1 As String)
'
'
Dim tt1 As Double
Dim time1 As Double
Dim time2 As Double


If IsNumeric(Left(t1, 4)) Then
    time1 = Left(t1, 4)
    time2 = Right(t1, 4)
        If time1 < time2 Then
            tt1 = (Int(time2 / 100) + (((time2 / 100) - Int(time2 / 100)) / 0.6)) _
            - (Int(time1 / 100) + (((time1 / 100) - Int(time1 / 100)) / 0.6))
        Else: tt1 = ((Int(time2 / 100) + (((time2 / 100) - Int(time2 / 100)) / 0.6)) + 24) _
            - (Int(time1 / 100) + (((time1 / 100) - Int(time1 / 100)) / 0.6))
        End If
    tt1 = tt1 / 24
    totalHours = tt1
Else: totalHours = 0
End If

End Function


Public Function TotalTotalHours(tt As Range)
Dim n As Double
Dim i As Integer
n = 0

For i = 1 To tt.Columns
  n = n + totalHours(tt.Cells(i, 1))
Next

TotalTotalHours = n
End Function

if i have...
----|------------|-----------|------------|------------|----
| A | B | C | D | E
1 | 0700-1900 | 0700-1900 | 0700-1900 | 0700-1900 |
----|------------|-----------|------------|------------|----

Cell E1 has =totalHours(A1) which gives 0.5
What i want is to be able to put =totalHours(A11) in E1 to give 2

any suggestions?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[SkipVought]

Hi,

Public Function totalHours(rng As Range)
    Dim t, tim1(1) As Date, i As Integer, r As Range
    For Each r In rng
        t = Split(r.Value, "-")
        For i = 0 To 1
            If IsNumeric(t(i)) Then
                tim1(i) = TimeSerial(CInt(Left(t(i), 2)), CInt(Right(t(i), 2)), 0)
            Else
                totalHours = 0
                Exit Function
            End If
        Next
        totalHours = Abs(tim1(1) - tim1(0))
    Next
End Function

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

Something like this should get you going - no real error checking and uses hard coded numbers to split the time but should be a decent start point:

Function GetHours(rng As Range)
Dim c As Range
Dim stTime, enTime
Dim TotTime

For Each c In rng
    
    stTime = TimeValue(Left(c, 2) & ":" & Mid(c, 3, 2))
    enTime = TimeValue(Mid(c, 6, 2) & ":" & Right(c, 2))
    
    TotTime = TotTime + (enTime - stTime)
    
Next c

GetHours = TotTime

End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question