Sum a custom function

[Gregorys05]

Hi guys,
I have the below function that calculates time in hours and mins if you type in seconds.

e.g 1200 secs - formula : =calctime(1200) cell value: 20:00

The problem i have is that if i have two columns with the formula in that i want to sum i cannot do this by just using the sum function.

Any ideas how i can do this at all??

Thanks

Function CalcTime(Seconds) As String
    Dim tData1, tData2
    If Not IsNumeric(Seconds) Then Exit Function
    If Seconds < 60 Then
        If Len(Seconds) = 1 Then
            CalcTime = "00:0" & Seconds
        Else
            CalcTime = "00:" & Seconds
        End If
    Else
        tData1 = RoundDown(Seconds / 60)
        tData2 = Seconds - tData1 * 60
        
        If Len(tData1) = 1 Then
            tData1 = "0" & tData1
        End If
        
        If Len(tData2) = 1 Then
            tData2 = "0" & tData2
        End If
        
        CalcTime = tData1 & ":" & tData2
    
    End If
End Function
Function RoundDown(Number)
    Dim tData1
    tData1 = InStr(Number, ".")
    If tData1 = 0 Then
        RoundDown = Number
    Else
        RoundDown = Left(Number, tData1 - 1)
    End If
End Function

 

[Sektor]

...if i have two columns with the formula in that i want to sum...

Pardon?

 

[Misca]

Your function returns strings, not actual time values.

Try changing your function to something like:

Function SecToTime(Seconds As String) As Date
Dim OneSec As Date
OneSec = CDate("0:00:01")
SecToTime = Val(Seconds) * OneSec
End Function

 

[Peter_SSs]

Do you really need a custom function? Could you not just use a standard formula like
=A2/1440 (where A2 holds your seconds - or did you really mean the first number is minutes? 1200 seconds = 20 minutes or 1200 minutes = 20 hours) and custom format the cell as "[hh]:mm" and then just use the normal SUM() function?

Excel Workbook
	A	B	C	D	E	F
1	Time 1	Time 2		Time 1	Time 2	Sum
2	60	67		01:00	01:07	02:07
3	75	20		01:15	00:20	01:35
4	2	100		00:02	01:40	01:42
5	1200	1200		20:00	20:00	40:00
SUM

 

[Comfy]

Try using

=SUM(VALUE(YourSumRange))

Confirmed with Ctrl+Shift+Enter

 

[Gregorys05]

Misca, that helps thank you, the only problem i have is that now if the value goes over 1 day 1440 then the calcution come back with #Value!

Any ideas

 

[Peter_SSs]

.. if the value goes over 1 day 1440 ..

I guess that confirms that you are really talking about converting minutes (not seconds) to hours and minutes.

 

[Gregorys05]

Comfy - Job Done. Nice one

 

[Peter_SSs]

Do you need an array formula? Wouldn't this (with appropriate cell references) do it?
=H2+I2

Mind you, I still cant see the need for a custom function to do the conversions.
Did you try the formulas I suggested in post #4?

Columns D:F below use standard formulas, columns H:J use your custom function. The results are the same.
Perhaps there is something different with your data that means these formulas don't give the results you want?

If for some reason you really want to stick with the custom function and use the SUM function, yet another (non-array) alternative is shown in columns L:N.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Time 1	Time 2		Time 1	Time 2	Sum		Time 1	Time 2	Sum		Time 1	Time 2	Sum
2	60	67		01:00	01:07	02:07		01:00	01:07	02:07		01:00	01:07	02:07
3	75	20		01:15	00:20	01:35		01:15	00:20	01:35		01:15	00:20	01:35
4	2	100		00:02	01:40	01:42		00:02	01:40	01:42		00:02	01:40	01:42
5	1200	1200		20:00	20:00	40:00		20:00	20:00	40:00		20:00	20:00	40:00
SUM