UDF (time) working but error when used in formula

[Kamolga]

Hi,

I have written

Function StopTime(Rng As Range) As String
StopTime = Format(Range("D10") + TimeValue("00:0" & Range("RngMin")) * (Rng.Row - 9), "hh:mm")
End Function

that calculates nicely the stoping time based on amoung of lines (Rng.Row) times an interval in minutes found in Range("RngMin").

If I write

[LEFT][COLOR=#222222][FONT=Verdana]=StopTime(B10)[/FONT][/COLOR][/LEFT]

instead of

=$D$10+TIME(,RngMin*(ROW(F10)-9),)

in a cell, I got the exact same value but if I replace

[LEFT][COLOR=#222222][FONT=Verdana]$D$10+TIME(,RngMin*(ROW(F10)-9),)[/FONT][/COLOR][/LEFT]

by StopTime(B10) in a formula with ifs, I have an error.

I guess it has something to do with the function being as String?

In addition, can someone confirm that the UDF will allways use D10 and rng fromits own sheet even if I copy the sheet multiple times?

Thank you in advance for any hint....
​

​

<strike></strike>

 

[Kamolga]

Got the UDF to work in formulae using dateadd instead of Format("hh:mm")

Function Stime(Rng As Range)
Dim interval As String
Dim amount As Double
Dim adate As Date
interval = "N"
amount = Range("RngMin") * (Rng.Row - 9)
adate = Range("D10")
Stime = DateAdd(interval, amount, adate)
End Function

Really easier to work with nested ifs (pauses, overnight, shifts,... cases)