formula to count number of months that have passed
More ways to skin cats in Excel than there cats in Excel.
"Dave F" wrote:
Wow, that's an interesting way to solve this, thanks.
Dave
--
Brevity is the soul of wit.
"Bob Phillips" wrote:
=IF($F7<0,"N/A",(YEAR(N$6)-YEAR($G7))*12+MONTH(N$6)-MONTH($G7))/$B$3*$F7
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Dave F" wrote in message
...
I'm trying to build a depreciation schedule for assets, in which
depreciation
is calculated on a monthly basis.
I have my table set up so that the column headings are months, i.e,,
10/31/2006, 11/30/2006, etc.
Each row in the table is devoted to a particular asset, which asset has an
asset creation date, and the difference between the asset creation date
and
the month in question would be the number of months over which
depreciation
has accumulated for the asset in question.
Example: Asset A was created on 9/26/2006. So, for October, 2006,
depreciation would be 1 month of depreciation. The depreciable life of
the
asset is 48 months. Now, I've figured out a formula that allows me to
calculate depreciation in this manner through the end of 2006. But when
1/31/2007 comes around I start to get errors. Here's the formula I'm
using:
=IF($F7<0,"N/A",(MONTH(N$6)-MONTH($G7))/$B$3*$F7)
F7 is the asset's cost, N6 is the column heading showing the relevant
month
(in this case, 10/31/2006), G7 is the asset creation date (9/22/2006), B3
is
the depreciable life of the asset (48 months).
So, here's the problem: when I get to 1/31/2006 (cell Q6), the difference
between that month and the month in 9/22/2006 is -8, which gives me absurd
depreciation results. Can someone figure out a way to tell Excel I'm
interested in counting months? The jerry-rigged way I have it here
obviously
doesn't work when the year turns.
Thanks,
Dave
--
Brevity is the soul of wit.
|