| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count number of months that have passed
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to count number of months that have passed
=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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to count number of months that have passed
Actually, I've answered this question myself:
=IF($F7<0,"N/A",DATEDIF($G7,N$6,"m")/$B$3*$F7) calculates correctly. Dave -- Brevity is the soul of wit. "Dave F" wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to count number of months that have passed
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to count number of months that have passed
Actually, the way is not Excel, it is just simple math. Excel is just the
vehicle for that math. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of uniques starting with a given letter? | Excel Discussion (Misc queries) | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Need a formula to calculate the number of months to pay off a loan | Excel Discussion (Misc queries) | |||
| Need formula to count number of books sold | Excel Worksheet Functions | |||
| Count number to reach a cumulative value | Excel Worksheet Functions | |||
Linear Mode
