If you have ever needed to allocate an amount over time, and split the amount into monthly columns based on the number of days, you’ll quickly realize that this simple idea is tricky to actually implement. As with anything in Excel, there are many ways to accomplish this task, and this post walks through one such method. If you prefer another method, please post a comment with your formula.
The goal is to take an amount, say $1,000, and allocate it into monthly columns between the allocation start and end dates, say 1/1 through 3/31. We would expect to see about a third of the amount in each monthly column for Jan, Feb, and March. We will allocate it based on the number of days in each month, so it won’t quite be equal thirds (January has more days than February). In addition to this primary goal, our formula should be consistent, so that we can fill it down and to the right and it should continue to work. Also, our formula should handle negative amounts in case there are any credit balances. And, our formula should return a zero if the allocation period is outside of the months reflected in our allocation table. Finally, our formula should handle partial month periods, and support allocation periods of say 1/15/14 through 4/15/14.
Although Excel has some built-in functions that get us kinda close, I haven’t discovered one that satisfies all of the goals outlined above. One such function that gets us close is the VDB function, however, it has a built-in assumption that the allocation is made in equal periods, and does not allow us to allocate an amount based on the number of days in a month. If your workbook doesn’t need this daily allocation, and simply requires whole-month periods, then the VDB function will greatly simplify your workbook and so it is definitely worth spending the time to check it out.
Note: the next few paragraphs walk through the logic of the formula, if however you just want the answer, skip down to the end of the post and feel free to download the sample file.
Rather than just post up the formula, I thought it would be helpful to build the formula together, piece by piece, so that if you decide to use it in your workbooks you will be able to adapt it and maintain it over time.
First of all, let’s have a look at the sample workbook, so that our objective is crystal clear.
The overall idea is quite simple. We take an amount, divide it by the number of days in the allocation period, and then multiply this daily amount by the number of days represented by each column in the allocation table. The related formulas in Excel are also quite simple, except for the part that figures out the number of days represented by each monthly column.
Let’s start with the easy calculated amounts, the number of days (Days) and the daily amount (DailyAmt). The number of days can quickly be determined by subtracting the Alloc End and Alloc Start dates. In cell F9, the formula would be:
This formula computes the number of days between two dates, which for most things is just fine. However, in our case, it creates a problem. Taking the simple case, if we wanted to allocate a value from 1/1/14 to 3/31/14, there are 90 days that we need to allocate. That is, 31 days for Jan, 28 days for Feb, and 31 days for March. However, a simple subtraction results in only 89 days because it does not include both date endpoints. This is an easy issue to overcome, since we can simply add 1 to the result. Our modified formula, shown below, will populate the Days column just fine.
Next is the DailyAmt column. This represents the amount to allocate daily, so it is just the total amount divided by the number of days. The formula in G9 is:
We can fill each of these formulas down, and the result is shown below.
So far, so good. Now, let’s get to the hard part: computing the number of days represented by each column.
We know that if we can compute the number of allocation days represented in each column, we can simply multiply it by the daily amt (DailyAmt). So, for now, we’ll focus only on computing the number of days represented in each column.
Let’s make an assumption that will help throughout this exercise. Let’s assume that the column headers in the monthly allocation table are date values that represent the last day of the month. So, the Jan column header is actually a date value that represents 1/31/14, and so on.
Rather than just post the formula to compute the days, I wanted to provide a more visual way to understand the mechanics. So, I created a series of screenshots to illustrate what each piece of the formula is doing.
If you think about it, there are several cases that we need our fancy formula to handle. We need it to handle the case where the current column header, we’ll call this the current period, falls between the allocation start and end dates. Our formula also needs to handle the case where the monthly column header is less than the start date. It also needs to work when the current period is greater than the end date. So, we’ll just go slow, and enhance the formula step by step.
Let’s take an easy case first, the case where the current column label is one month into the allocation period. For the series of screenshots below, they all assume that the allocation period is the three-month period from 1/1 (StartDate) to 3/31 (EndDate). If the current period (CurPer) is 1/31, then we could think about the number of days as the line segment A pictured below.
The number of days in Segment A is easily computed by subtracting the date endpoints.
A = CurPer - StartDate A = 1/31 - 1/1 (Note: technically this is off by one day; we'll address this issue shortly)
So far so good? OK, let’s consider another case. The case where the monthly column date is earlier than the allocation start date. For example, a current monthly column date of 11/30, as illustrated below.
In this case, our formula (CurPer – StartDate) will result in a negative number of days. This is an easy fix. The MAX function returns the largest argument value. For example, MAX(100,0) returns 100, and MAX(-200,0) returns 0. So, we can easily update the formula to prevent a negative number from being returned by wrapping a MAX function around it.
A=MAX(CurPer-StartDate,0) A=MAX(11/30-1/1,0) A=0
Another case our formula needs to handle is the case where the current period is more than one month into the allocation period. For example, when CurPer = 2/28. Consider the screenshot below.
Our existing formula (CurPer – StartDate) returns the cumulative number of days between the current period and the start date. Since we only want the number of days within the current monthly column, we need to subtract the number of days prior to the current period. This is represented by Segment C above. Segment C can be computed by subtracting Segment B from Segment A.
C=A-B A=CurPer-StartDate = 2/28-1/1 B=PriorPer-StartDate = 1/31-1/1
The PriorPer value is easily computed with the EOMONTH function. If you haven’t explored the EOMONTH function, here is a post that walks through it. To avoid negative day values, we’ll once again use the MAX function.
The final case we need to handle is the case where the current period falls after the end date, such as when CurPer = 5/31. This is illustrated below.
Our existing formula, which computes Segment C, would return a non-zero value even though there should be no allocation since the current period date is after the last day of the allocation period. Thus, we subtract from it the number of days in the current period that are in excess of the EndDate, represented above by Segment F. The desired formula result is C-F to return zero days.
F=D-E D=CurPer-EndDate E=PriorPer-EndDate
Again, in order to prevent negative days from being returned, we wrap a MAX function around these, and, once again, we compute the PriorPer date using the EOMONTH function.
If we put all of this into a formula, and then fill the formula down and to the right, the result of the number of days computed for each column in the allocation table follows.
The Excel formula used in cell H9 follows:
Although it may appear confusing, it is simply computing the steps we did above. We compute Segment C (the first line in the formula above) and subtract from it Segment F (the second line).
The MAX functions are in there just to ensure we don’t return a negative number of days, and the EOMONTH functions are in there just to compute the prior period date values.
You’ll notice that we still need to address the fact that we are off by one day in the first allocation period column. That is, the 1/31 column improperly reflects 30 days, when it should be 31 days. This is because 1/31 – 1/1 is 30 days, but, we need to make the allocation based on 31 days. We need to update our allocation table formulas to add one to the number of days for the first allocation period. There are many ways to accomplish this, one way is to use a comparison formula that determines if the CurPer period is equal to the StartDate period. A comparison formula returns TRUE, and TRUE is treated as a 1 by Excel’s calculation engine. So, if we added the following to the end of our formula, we should be good:
Translating the logic above into an actual Excel formula results in the updated formula below.
=(MAX(H$8-$C9,0)-MAX(EOMONTH(H$8,-1)-$C9,0)) -(MAX(H$8-$D9,0)-MAX(EOMONTH(H$8,-1)-$D9,0)) +(EOMONTH(H$8,0)=EOMONTH($C9,0))
The above formula takes Segment C minus Segment F and then adds 1 if the last day of the current column period is equal to the start date period. The updated allocation table is shown below.
Now that the number of days is accurate, all we need to do is multiply them by the DailyAmt stored in cell $G9. The resulting worksheet is shown in the screenshot below.
The Excel formula in H9 follows:
=$G9* ((MAX(H$8-$C9,0)-MAX(EOMONTH(H$8,-1)-$C9,0)) -(MAX(H$8-$D9,0)-MAX(EOMONTH(H$8,-1)-$D9,0)) +(EOMONTH(H$8,0)=EOMONTH($C9,0)))
And that my friend is one way to allocate an amount into monthly columns. There are so many other ways, I’d love to hear your preferred method…post your formula into the comment box below.
Please download the sample file if you’d like to check it out.
- Allocation (original file)
- Allocation2 (this updated version has a sheet that excludes weekends from the allocation)
- Allocation3 (this updated version has a sheet that does daily allocation columns)
- Allocation4 (this updated version has a sheet that does monthly allocation columns)
- Allocation5 (this updated version has a sheet that includes yearly allocation columns)
- Allocation6 (this updated version has a sheet that computes weekly hours)