stopping a value

[Matt Brown]

Is it possible to stop a value when using the datediff function?

I am using several functions to calculate depreciation on an asset item.
The one which i need to try and stop is the datediff function which is currently in a forms text box.
When it reaches the depreciation life of an item it needs to stop at this point.
i.e. If the depreciation life is say 8 years, when the datediff matches this amount it need not go any further, is this possible?

 

[Rich]

=Iif(DateDiff("yy",[StartDate],[EndDate])>=8,8,DateDiff("yy",[StartDate],[EndDate]))

 

[Matt Brown]

Thanks Rich, will give that a try.

Matt

 

[Matt Brown]

Hi Rich ,

Sorry mate seemed to be struggling on this.

I have attached a screen shot of the form that i am currently re-designing.

I shows my depreciation workings out.
I need to be able to get the Years elapsed and the Cur Dep Val to halt when they both reach zero.

 

[The_Doc_Man]

There are a few ways to handle this.

First, the IIf on the date exceeding the amortization schedule lifetime as suggested by Rich.

Second, put an IIf on the computed value so that if it comes back <0, you just return 0.

Third, write some VBA code to do what you want as a public function. Make the item's base date, depreciation rate, and other parameters become formal arguments. Then return values from the function based on the results.

Fourth, add a reference to the Excel library. They have some canned functions that might do depreciation scheduling correctly. You could call one of those functions if you have a reference to the .DLL that holds it.

 

[Matt Brown]

Hi Doc_man,

Thanks for the reply,

I have tried the following two functions, DDB Function, SYD Function but they arn't quite what i am after, they only return a single value and not a yearly decllining figure.
I presume that these are the functions you were refering to.


Okay will have another go, writing out the function could be the better way to go as you suggested.

cheers

Matt

 

[The_Doc_Man]

I think the problem is that the really good amortization functions are in the Analysis Pak, which is not installed by default. I've never really worked with that. I tend to be in the more scientific math where I am. Normal distributions, probabilities, traditional statistics of populations, and trend-line analysis. So I'm not the best source.

Look at Help for EXCEL functions AMORLINC, AMORDEGRC, DB, DDB, VDB, and SYD as a whole to see if they provide help links to something that might be helpful.

 

[Matt Brown]

Thanks for the info Doc_man,

Will take a look at these.

Just been playing around wiht Rich's suggestion using the Iif statement:

=IIf(DateDiff("yyyy",[DATE WHEN PURCHASED],Now())>=[DepLife],[DepLife],DateDiff("yyyy",[DATE WHEN PURCHASED],Now()))

I removed the static 8,8 and replaced with the DepLife refs.
(DepLife is the amount of years the item depreciates over)

Does this look right as i seem to be getting some strange returns on the values or have i miss-read the iif statement?
cheers

Matt