UDF (time) working but error when used in formula

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,185
Hi,

I have written
Code:
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
Code:
[LEFT][COLOR=#222222][FONT=Verdana]=StopTime(B10)[/FONT][/COLOR][/LEFT]
instead of
Code:
=$D$10+TIME(,RngMin*(ROW(F10)-9),)
in a cell, I got the exact same value but if I replace
Code:
[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>
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Got the UDF to work in formulae using dateadd instead of Format("hh:mm")
Code:
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)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top