This came from one of my past participants. She was trying to allocate the cost of items across different periods, in her case, across financial years. Manually done, the steps should be: identify the start and end date, apportion the cost for these 2 periods. If the duration cross over more than 2 periods, then those period in between the 2 dates should have a constant periodic (yearly) cost. It looked like a pretty straight forward solution. So I thought. It turned out that the solution is

a lot more complicated and required the use of more complex formulas. If this was only for one cost item, I would have stopped pursuing a solution. But this was for multiple lines of cost, so the effort to do this manually was going to snowball. Calculating multiple lines of cost will be taxing for the person and increased the tendancy to make human errors as well. So I persisted and wa glad that a solution that was easy to manage could be found in the end and was described below. Before that, let me gave you the names of the data I used in my explanation.

1. Start with a HLOOKUP formula to look up for the start date across the starting date of all the periods. It will identify which period it fell into.

2. Compare this with the period starting date in row 1.

HLOOKUP date was less than the item start date, it will return false, an indication that the cost item does not start in that year.

3. Create another HLOOKUP without the square brackets [=HLOOKUP($B4,$F$1:$K$2,2)] and this time, lookup for the cost end date in the ending date of each year. Compare the result with the ending date of each year. If the ending date of the period is greater than the cost end date, it means that the cost is completed.

4. Now that the duration is identified, we can calculate the cost. For the first period of the cost allocation, it is not a full year. For in between periods, it is the starting date of the period. So we have to find the later date between the cost start date and period start date and take whichever is later. This can be achieved with the max formula. There are 12 months in each period and the cost has to be allocated on a per month basis. Hence the formula need to identify the start date of the month (either for the starting period of the year or the cost start date). The formula is

EOMONTH(MAX(F$1,$A4),-1)

5. The last period where the cost ends, the ending date of the cost is earlier than the period end date. In between periods, the period end date is earlier. We can use the min formula to identify it is the period end date or the cost end date. As it is calculating cost on a monthly basis, we need to identify the last day of the month for the date. The formula is

EOMONTH(MIN(F$2,$B4),0)

6. Using the 2 EOMONTH formulas, you can find the number of months for the cost allocation in each period using the DateDiff formula. Combining the formulas to gether, we get

DATEDIF(EOMONTH(MAX(F$1,$A4),-1),

EOMONTH(MIN(F$2,$B4),0),"m")

7. With the number of months calculated, we can then calculate the cost apportioned to each period by multiplying with the monthly cost for the items

DATEDIF(EOMONTH(MAX(F$1,$A4),-1),

EOMONTH(MIN(F$2,$B4),0),"m")*$E4

8. This formula should be applied for the periods between the cost start and end date. To exclude periods that fall outside this period, we can use the 2 sets of HLOOKUP formula we have created earlier. We can use the IF formula to combined all of them together to get this complete formula.

=IF(AND(HLOOKUP($A4,$F$1:$K$2,1)=F$1,HLOOKUP($B4,$F$1:$K$2,2)>=F$2)

,DATEDIF(EOMONTH(MAX(F$1,$A4),-1),EOMONTH(MIN(F$2,$B4),0),"m")*$E4,0)

=IF(AND(HLOOKUP($A4,$F$1:$K$2,1)<=F$1,

HLOOKUP($B4,$F$1:$K$2,2)>=F$2)

,DATEDIF(EOMONTH(MAX(F$1,$A4),-1),

EOMONTH(MIN(F$2,$B4),0),"m")*$E4,0)

9. Note the $ signs for each reference. With the proper $ placement, you can safely copy the formulas across the periods and down the rows for all the cost items.

Share this page:

Copyright © advanced-excel.com 2007 - 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft