How to calculate values related to specific dates

**MyStix01** · 05-16-2018, 09:58 PM

Hi,

I am creating a spreadsheet for my depreciation schedule and I'm having problems on calculating the depreciation expense base on the number of days in a month.

Depreciation expense is calculated as follows :

Straightline = Acquisition Cost x (1/Depreciable Life) x (No.of days in the reporting month/No. of days in a year) - This is since i'm calculating and reporting on a per month basis.
Diminishing = Opening Written Down Value x (2/Depreciable Life) x (No.of days in the reporting month/No. of days in a year)

Opening Written Down Value is Acquisition cost less all the previous depreciation expense (Accumulated Dep. Exp.)

Note : When I started working on the original spreadsheet, they only have the Depreciate Rate so I just tried to work back the Depreciable Life. Originally. it should be the other way around.

Attached is my spreadsheet for easy reference.

Here is the problem,

1. First, I need a formula under K4:5 - U4:5 of Sheet 2 and 3 that will determine the reporting year. So basically, once I input in Sheet 1 cell F2 the reporting year, then it should automatically feed through. Ex:
2017 - Jul 2016 - Jun 2017
2018 - Jul 2017 - Jun 2018
2019 - Jul 2018 - Jun 2019

2. I need a formula in Sheet 1 cell C5:C9 where in if I input the month in cell E2, it will search for the corresponding month in Sheet 2 and Sheet 3 and return that value.

3. I need an integrated formula for Straightline depreciation ie. cell K6:V6 that is able to :
- Determine if the asset is acquired within the reporting period, because if that is the case, then only a proportional amount should be expense on the month it is acquired. Ex. Next Gen Design and Construct. On Jan 2018, the expense should only be from 28/01/2018-31/01/2018.
- Determine if the asset is to be fully depreciated within the reporting period, because if that is the case, then on the month of its end date, the expense should equal to what is remaining to fully depreciate the asset. Ex. A&STech was acquired on 20/02/2015, with 3.03 yrs as its Depreciable Life, it is set to fully depreciate on 02/03/2018. If you try to compute this, $74,495.00 * 33% * 2/365 = 134.70. This amount gives a $4.85 variance if the total expenses were reconciled with the acquisition cost. That is why, for the end date of the asset, instead of calculating it on a per days basis, I would prefer the formula to just determine what is the remaining amount to expense.

4. I need an integrated formula for Diminishing depreciation ie. cell K6:V6 that goes the same with Straighline. The only difference here is, the expense is based on the Opening Carrying Value and not on the Acquisition Cost so it should be able to account as well for the previous amount expense. So, the formula should also be able to identify
- That if in case there is no Opening Carrying Value on 30/06/2017 because the asset is acquired after that date. Then it should instead compute base on the Acquisition Cost, that is being the carrying value at the start of the year it is acquired.
- Lastly, at the end term of the asset. It should as well, just force the amount to equal to what is remaining for that asset.

I hope this all makes sense. And I'd really appreciate if someone would take the time to help me on this. I'd gladly discuss this thoroughly with anyone who would take the time.

Thank you so so much!!!

**JohnTopley** · 05-17-2018, 12:19 AM

Parts 1. and 2.

in K4

=DATE(SUMMARY!F2-1,7,1)

in L4 and copy across

=EDATE(K4,1)

in SUMMARY

in C5

=INDEX(STRAIGHTLINE!$K$6:$AC$10,MATCH("Totals",STRAIGHTLINE!$B$6:$B$10,0),MATCH(DATE($F$2,MONTH($E$2&0),1),STRAIGHTLINE!$K$4:$V$4,0))

in D5
=INDEX(STRAIGHTLINE!$D$6:$D$10,MATCH("Totals",STRAIGHTLINE!$B$6:$B$10,0))

in E5

=INDEX(STRAIGHTLINE!$Z$6:$Z$10,MATCH("Totals",STRAIGHTLINE!$B$6:$B$10,0))

Change STRAIGHTLINE for DIMINISHING in Row 6

**JohnTopley** · 05-17-2018, 12:38 AM

Question from an accounting ignoramus: how do we (I!) translate the ".03" in figure of dep. years in 3.03 years into months, so in your file acquisition in Feb 2015 terminates in March 2018.

I assume a 3 year would end Jan 2018?

**MyStix01** · 05-17-2018, 01:02 AM

Originally Posted by JohnTopley

Question from an accounting ignoramus: how do we (I!) translate the ".03" in figure of dep. years in 3.03 years into months, so in your file acquisition in Feb 2015 terminates in March 2018.

I assume a 3 year would end Jan 2018?

Hi John,

I actually just tried to convert the 3.03 yrs into days using google, 3.03yrs = 1105.95 days or 1106 rounded off. Then I added the 1106 days to Feb 2015 that's why I came up with the Mar 2018 instead of Jan 2018 - which definitely what would you naturally think of if we are to just ignore the .03. If there is a formula in excel that can convert years to days, then I think that would help. In any case, if it's quite impossible to do so, I'll settle with the rounded years, i.e 3 yrs.

Thank yo so much!

**JohnTopley** · 05-17-2018, 01:16 AM

Will look at the conversion.

in "Straightline" to get the last value (Mar 18) you have

=D8-74355.45

where does the 74355.45 come from?

**MyStix01** · 05-17-2018, 01:34 AM

Originally Posted by JohnTopley

Will look at the conversion.

in "Straightline" to get the last value (Mar 18) you have

=D8-74355.45

where does the 74355.45 come from?

74355.45 is the total of all amounts already expensed. So that would be = (Acquisition Cost - Opening WDV) + Expenses for July-Feb ie. 74495-16506+(16366.45) ie. D8-I8+(K8:R8)

The end date might vary from time to time depending on when the asset will be purchased and when will be it's term end date so I guess that's why it is important that the formula can identify when will be the end date. So that by the time it reaches that date, it can just return a value that is equal to the amount that will complete the acquisition cost. I hope that makes sense?

**JohnTopley** · 05-17-2018, 02:31 AM

For 3.

in K2

=IF(OR(K$4 < EOMONTH($E6,-1)+1,K$4 > EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1),"",IF(K$4=EOMONTH($E6,-1)+1,$D6*$H6*((((EOMONTH(K$4,0))-$E6+1))/365),IF(K$4=EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1,$I6-SUM($J6:J6),$D6*$H6*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

Copy across and down

I am sure this can be shortened but it works on your file.

NOTE: column J must be blank (as now)

I'll look at 4 shortly.

**FlameRetired** · 05-17-2018, 02:40 AM

This is another way to do the STRAIGHTLINE. In K2 filled down and across.

=IFERROR(INDEX(FREQUENCY(ROW(INDEX(A:A,$E6):INDEX(A:A,$E6-1+$D6/($H6/365*$D6))),EOMONTH($E6,ROW($1:$100)-1))*$H6/365*$D6,
MATCH(EOMONTH(K$4,0),INDEX(EOMONTH($E6,ROW($1:$500)-1),0),1)),0)

It returns all the same values you expect except for S8. I get $67.35. You are expecting $139.55.

**JohnTopley** · 05-17-2018, 02:51 AM

For 3.

in K2

=IF(OR(K$4 < EOMONTH($E6,-1)+1,K$4 > EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1),"",IF(K$4=EOMONTH($E6,-1)+1,$D6*$H6*((((EOMONTH(K$4,0))-$E6+1))/365),IF(K$4=EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1,$I6-SUM($J6:J6),$D6*$H6*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

Copy across and down

I am sure this can be shortened but it works on your file.

NOTE: column J must be blank (as now)

I'll look at 4 shortly.

**JohnTopley** · 05-17-2018, 02:54 AM

for 4.

in K6

=IF(OR(K$4 < EOMONTH($E6,-1)+1,K$4 > EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1),"",IF(K$4=EOMONTH($E6,-1)+1,($AD6-SUM($J6:J6))*$H6*((((EOMONTH(K$4,0))-$E6+1))/365),IF(K$4=EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1,$AD6-SUM($J6:J6),($AD6-SUM($J6:J6))*$H6*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

Used a helper in AD

in AD6

=IF($I6<>0,$I6,$D6)

See attached: STRAIGHTLINE(2) & DIMINISHING(2)

**JohnTopley** · 05-17-2018, 06:03 AM

@Dave,

"Cheeky" change to the maths!

=IFERROR(INDEX(FREQUENCY(ROW(INDEX(A:A,$E6):INDEX(A:A,$E6-1+1/($H6/365))),EOMONTH($E6,ROW($1:$100)-1))*$H6/365*$D6,MATCH(EOMONTH(K$4,0),INDEX(EOMONTH($E6,ROW($1:$500)-1),0),1)),0)

For your query on difference in last value ( S8) , see post #6

John

**FlameRetired** · 05-17-2018, 08:54 PM

@ John

Thanks for both those. I didn't recognize the significance of nickeyme02's comments at first reading.

**MyStix01** · 05-18-2018, 02:19 AM

@ John & @ Dave,

Thanks to the both of you for the help. I really really appreciate your time. As I have actually a number of assets to depreciate, I'm still trying to do some test checks on the formulas . I'll surely let you know how it goes. But just to thank you in advance for helping me out on this. Have a blessed day!

**MyStix01** · 05-18-2018, 03:24 AM

@ John

Your formulas works really well, I just have one issue though right now, for both Straigthline and Diminishing, when the acquisition date is the last day of the month, the formula is giving me an amount to expense for the month when I think it shouldn't be the case. Say for example, the Next Gen Design, if I change the acquisition date to 31/01/2018, there is a 0.86 expense for Jan. It should give me zero instead because the depreciation period should start from 01/02/2018 to end date. Are you able to kindly twitch it a little to fix that?

Thank you thank you so much for the help!

**JohnTopley** · 05-18-2018, 03:35 AM

Try

=IF(OR(K$4<=EOMONTH($E6,-1)+1,K$4>EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1),"",IF(K$4=EOMONTH($E6,-1)+1,$D6*$H6*((((EOMONTH(K$4,0))-$E6+1))/365),IF(K$4=EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1,$I6-SUM($J6:J6),$D6*$H6*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

**MyStix01** · 05-21-2018, 02:15 AM

Hi John,

The new formula is not calculating for the whole month of when it was acquired. Ex. acquired on 20/07/2017, it will not give me any amount for 21/07/2017-31/07/2017.
I just need it to not calculate on the date of acquisition i.e 20/07/2017. So that if any asset is acquired on the last day of the month, ex. 31/07/2017, it wont give any amount. Easier said, the formula should start calculating the expense the day after the date of acquisition. I hope that makes sense? This is the only thing left and your formula will work really really amazing on my spreadsheet. I hope you could help me again. Thank you so much!

**JohnTopley** · 05-21-2018, 06:48 AM

Try

in "Straightline"

=IF(OR(K$4 < EOMONTH($E6,-1)+1,K$4 > EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1),"",IF(K$4=EOMONTH($E6,-1)+1,$D6*$H6*((((EOMONTH(K$4,0))-$E6))/365),IF(K$4=EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1,$I6-SUM($J6:J6),$D6*$H6*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

in "Diminishing"

=IF(OR(K$4 < EOMONTH($E7,-1)+1,K$4 > EOMONTH($E7+ROUNDUP($G7*365,1),-1)+1),"",IF(K$4=EOMONTH($E7,-1)+1,($AD7-SUM($J7:J7))*$H7*((((EOMONTH(K$4,0))-$E7))/365),IF(K$4=EOMONTH($E7+ROUNDUP($G7*365,1),-1)+1,$AD7-SUM($J7:J7),($AD7-SUM($J7:J7))*$H7*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

**MyStix01** · 05-22-2018, 04:05 AM

Originally Posted by JohnTopley

Try

in "Straightline"

=IF(OR(K$4 < EOMONTH($E6,-1)+1,K$4 > EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1),"",IF(K$4=EOMONTH($E6,-1)+1,$D6*$H6*((((EOMONTH(K$4,0))-$E6))/365),IF(K$4=EOMONTH($E6+ROUNDUP($G6*365,1),-1)+1,$I6-SUM($J6:J6),$D6*$H6*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

in "Diminishing"

=IF(OR(K$4 < EOMONTH($E7,-1)+1,K$4 > EOMONTH($E7+ROUNDUP($G7*365,1),-1)+1),"",IF(K$4=EOMONTH($E7,-1)+1,($AD7-SUM($J7:J7))*$H7*((((EOMONTH(K$4,0))-$E7))/365),IF(K$4=EOMONTH($E7+ROUNDUP($G7*365,1),-1)+1,$AD7-SUM($J7:J7),($AD7-SUM($J7:J7))*$H7*(((EOMONTH(K$4,0))-((EOMONTH(K$4,-1)+1))+1)/365))))

Yey, this now works and everything now reconciles with my testing spreadsheet! I'm so happy!!! Can't believe that this is possible.
Just another thing though, under Sheet 1, the formula for Accumulated Depreciation, I know that it is just taking the total amount for the year i.e Jul-Jun. Can we make it like for example, If I enter the Feb amount, the accumulated depreciation that would reflect in Summary sheet would be -

=(Acquisition Cost/Opening WDV) - whichever has an amount LESS (Sum of Depreciation from July to (whichever month is in cell E2 of Sheet1))

Reason behind is, so that the Summary table will only give me the amounts for the month even though Sheet 2 and 3 is calculating expenses already for the whole year. I hope it makes sense.

Again thank you so much, you're so great!!!! Have a blessed day ahead!

**JohnTopley** · 05-22-2018, 04:17 AM

There is no "Sheet1"! so I don't know to what you are referring.

Please post a file with example calculation as you say enter a value for Feb but use date in E3

.

**MyStix01** · 05-23-2018, 12:27 AM

Oh, apologies for the confusion, so what I need is for the formula in Summary Sheet E5:E6 to return the total value of Accumulated Depreciation up to the month I am reporting for.

- Accumulated Depreciation is calculated as :
1. If asset is acquired after the Opening Written Down value date (I5)
= sum of expenses for the current year, i.e (K6:V6)
2. If asset is acquired on and before the Opening Written Down value date (I5)
= Acquisition Cost (D6) - Opening WDV (I6) + Sum of expenses for the current year (K6:V6)

- Reporting month is determined as the value in cell E2 of Summary sheet.

So in attached sample spreadsheet, since I am reporting for Feb, I need the formulas to just calculate from column K to column R both for points 1 and 2.
and if I change Feb to March, then it should be from column K to column S and so on and so forth,

Hope this makes. I entered the should be value for easier reference.

**JohnTopley** · 05-23-2018, 01:18 AM

Why for "Straightline" have you omitted D7/I7 from your calculation?

Please Login or Register  to view this content.

Is that because there is no value in I7?

Similar for "Diminishing"

**MyStix01** · 05-23-2018, 01:34 AM

Originally Posted by JohnTopley

Why for "Straightline" have you omitted D7/I7 from your calculation?

Please Login or Register  to view this content.

Is that because there is no value in I7?

Similar for "Diminishing"

Hi John, yes it is because of that.

As for your formula, it seems like there is no if formula that links it to cell E2 of Summary Sheet, because I need it to automatically feed through the details. so that if I change feb to march, it will calculate this way.

=IF(E6<I$5,D6-I6+SUM(K6:V6),IF(I6>0,SUM(K6:V6),0))

The difference is I need it to calculate the totals for all. So I think it should be index? and it should also be linked with the cell E2.

Does that makes sense?

**JohnTopley** · 05-23-2018, 01:46 AM

These give the results in your posted file:

=SUMIF(STRAIGHTLINE!$I$6:$I$8,">0",STRAIGHTLINE!$D$6:$D$8)-SUM(STRAIGHTLINE!$I$6:$I$8)+SUM(STRAIGHTLINE!$K$6:$K$8:INDEX(STRAIGHTLINE!K6:V8,,MATCH(SUMMARY!$E$2,STRAIGHTLINE!$K$4:$V$4,0)))

=SUMIF(DIMINISHING!$I$6:$I$8,">0",DIMINISHING!$D$6:$D$8)-SUM(DIMINISHING!$I$6:$I$8)+SUM(DIMINISHING!$K$6:$K$8:INDEX(DIMINISHING!$K$6:$V$8,,MATCH(SUMMARY!$E$2,DIMINISHING!$K$4:$V$4,0)))

EDIT: second formula corrected.

**MyStix01** · 05-23-2018, 02:01 AM

Originally Posted by JohnTopley

These give the results in your posted file:

=SUMIF(STRAIGHTLINE!$I$6:$I$8,">0",STRAIGHTLINE!$D$6:$D$8)-SUM(STRAIGHTLINE!$I$6:$I$8)+SUM(STRAIGHTLINE!$K$6:$K$8:INDEX(STRAIGHTLINE!K6:V8,,MATCH(SUMMARY!$E$2,STRAIGHTLINE!$K$4:$V$4,0)))

=SUMIF(DIMINISHING!$I$6:$I$8,">0",DIMINISHING!$D$6:$D$8)-SUM(DIMINISHING!$I$6:$I$8)+SUM(DIMINISHING!$K$6:$K$8:INDEX(DIMINISHING!$K$6:$V$8,,MATCH(SUMMARY!$E$2,DIMINISHING!$K$4:$V$4,0)))

EDIT: second formula corrected.

Hi, it's returning a #N/A value in me, see attached.

**JohnTopley** · 05-23-2018, 02:23 AM

Sorry forgot to say : need to change date in E2 to Excel data: 01/02/2018.

ALWAYS use proper dates and format as Day/Month/Year as required

**JohnTopley** · 05-23-2018, 02:26 AM

See the attached

**MyStix01** · 05-23-2018, 03:19 AM

I know I'm already asking for too much but can we include in the formula a function in which it will not include the amounts for totals under column K to V, because I need to extend those all the way down to V200, and whenever I do so, it is also summing up the amounts in the totals row.

**JohnTopley** · 05-23-2018, 04:27 AM

See formulas in H5

Please Login or Register  to view this content.

and H6

Please Login or Register  to view this content.

Formulas in Straightening need changing to replace "null" with 0

Please Login or Register  to view this content.

**MyStix01** · 05-23-2018, 05:14 AM

Please dont hate me for this, but since I cant upload here the original spreadsheet, whenever I copy your formula, I encounter another problem, but I do really believe that this is the last thing and your formulas would do wonderful works on my sheet.
Here it goes, you can see in the attached file that I entered another row highlighted in red both for Straightline and Diminishing.
The problem is, the current formula you have provided for Accumulated Dep in Summary Sheet wouldnt include the Accumulated Depreciation for previous assets that were fully depreciated already and I actually need them to reflect in here.
So in Summary sheet E5 and E6, I have to add that in the formula as you can see. I hope we can include a function as well that can determine the value in column Z of both straightline and diminishing, I think the criteria should be something like if the opening wd = 0 and asset is fully depreciated based on acquistion date and depreciation life then the formula in E5 and E6 (summary sheett) should include the value in column Z Straightline, Diminishing respectively.

Also, is there a way I can delete the value F2 in Summary Sheet, and the formulas for cell K4:V4 will just then based on E2? Just to eliminate double inputting of the year. I change e2 format to reflect that. But just to stress out that if I enter
1. Jul 2017 in cell E2 - Summary Sheet, Cell K4:V4 in Straightline should remain Jul 2017 - Jun 2018
2. Jan 2018 in cell E2 - Summary Sheet, Cell K4:V4 in Straightline should remain Jul 2017 - Jun 2018
3. Jul 2018 in cell E2 - Summary Sheet, Cell K4:V4 in Straightline should remain Jul 2018 - Jun 2019
4. Jan 2019 in cell E2 - Summary Sheet, Cell K4:V4 in Straightline should remain Jul 2018 - Jun 2019

Again, thank you so so much. Thank you wouldnt be enough for all the help. God bless.

**JohnTopley** · 05-23-2018, 06:42 AM

Removing F2

change I5,

=DATE(YEAR(SUMMARY!E2)-1,7,)

K4

=DATE(YEAR(SUMMARY!$E$2)-1,7,1)

**MyStix01** · 05-23-2018, 07:15 PM

This is fantastic! Thank you so much for all the help. I couldn't have done anything without your efforts. God bless and again, thank you.

**AliGW** · 05-30-2018, 02:31 AM

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Re: How to calculate values related to specific dates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculate diff in dates for specific ID

[SOLVED] Calculate time related values as minute average

[SOLVED] Calculate Amounts for Specific Dates

Bookmarks

Bookmarks

Posting Permissions