How to calculate values related to specific dates

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!!!