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

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.

## The Walk-through

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:

=D9-C9

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.

=D9-C9+1

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:

=B9/F9

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:

=(MAX(H$8-$C9,0)-MAX(EOMONTH(H$8,-1)-$C9,0)) -(MAX(H$8-$D9,0)-MAX(EOMONTH(H$8,-1)-$D9,0))

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:

+EOMONTH(CurPer)=EOMONTH(StartDate)

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.

## Sample File

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)

I’ve been looking for a spreadsheet to Allocate my sales for years…. thank you!

Awesome resource. Thanks for posting.

Great formula. I used it to allcoate the amount of days an activity takes place per month. I was wondering if there was something I could add to the formula exclude weekends. Thank you for your help.

Danielle,

One way to exclude the weekends from the allocation would be to rely on the built-in NETWORKDAYS function. This function computes the number of workdays, excluding weekends, between two dates, inclusive. Thus, we could use this function, instead of basic subtraction, to compute both the total allocation days and the monthly allocation days, for example:

=NETWORKDAYS(C11,D11)

I’ve uploaded an updated version of the sample workbook to include a new worksheet named “Weekdays” to illustrate. I’ve basically replaced the simple subtraction used previously with the NETWORKDAYS function. Hope it works out well for your purpose…thanks!

This is so close to exactly what I am looking for! We are an arts center & want to capture how many times a class meets in a month. (Ultimately, we want to capture how many students visit each month, and that will be easy – just multiply the # of students by # of class meetings, but I can’t figure out how to calculate class meetings.) Here is what data we have:

-Start Date

-End Date

-Number of Students

-Day(s) of the Week the Class Meets

But we also need to take into consideration:

-Some classes meet more than once a week

-Some weeks are “skipped” because of a holiday

Is there a way to use your method above with these added complications? Thank you SO MUCH for any advice or assistance!

Me again! I played around with the spreadsheet you posted for Danielle & tried changing NETWORKDAYS to NETWORKDAYS.INTL (that I learned about in my online searches) to allow me to account for multiple classes per week and holidays. It is ALMOST working!!! However, there’s an error in there somewhere that I just can’t find. For some reason in *some* of the columns that are performing the calculation, it is adding a day. I have the spreadsheet that I would be happy to upload/email. So happy to be making progress at last! Thank you for this breakthrough & thank you in advance for your further help!

Susannah,

I see you posted this yesterday…sorry for my delay…did you get it working?

Thanks

Jeff

Jeff,

Thank you so much for your reply! Maybe… It looked like it was working, then I made some other changes to the worksheet that caused some problems & I’ve been trying to sort those out… It is showing me errors now, but it could be that once I fix the other problems I’m having, those will go away… I will keep you posted! If it turns out it’s working, I’d be glad to share the formula if you are still interested in having it! Just not sure of the best way to show it?

Thanks again,

Susannah

Yes, would love to post your modifications in case they could help others 🙂

Keep me posted….thanks!!

Hi Jeff – This worksheet it exactly what I’ve been looking for! Thanks for posting this! One issue I found that is present in the Weekdays sheet that is not in the Allocation sheet is:

Amount = 100

Alloc Start = 3/31/14

Alloc End = 4/1/14

Results in 100 being allocated to 3/31/14 and 0 to 4/30/14 columns? Any idea what is causing the issue?

Thank you!

I believe I found the solution to my previous post. If you subtract 1 from every instance of networkdays() so networkdays()-1 then it seems to work correctly.

Brian,

Thanks for not only identifying this issue but also resolving it…much appreciated!!

Thanks

Jeff

Excellent file. It helped me a lot.

Thank you very much.

Amazing solution. Could you please tell me the formula do allocate daily. I am getting error, because I want to use this formula for daily forcasting. Many thanks

GK,

Thanks! Sure, happy to help. I’ve added a new version (Allocation3) above that includes a new sheet for daily allocations.

Hope it helps!

Thanks

Jeff

This is amazing. I have been trying to compute deferred revenue for a while and this formula allocates the sales value to the respective period in no time!

Glad it helped 🙂

Thanks,

Jeff

I love this! Thanks :). Is there a way to spread the cost evenly and into the right months

Sure, doing the allocation by month instead of day just requires a minor tweak. I’ve added a new tab to the file which demonstrates the mechanics. It is available above as Allocation4.

Hope it helps!

Thanks

Jeff

Hi

how about the same in pivot??

I need to Allocate an Amount into Monthly Columns depending on the start and the end dates of the period

For example

project start date end date sum

aaa 12/02/2015 15/03/2015 1000

bbbb 03/03/2015 31/03/2015 3500

cccc 01/05/2015 05/07/2015 6000

dddd 24/04/2015 15/01/2016 7000

Is there a way to do it?

thanks

I’m not sure at the moment how to build such a PivotTable…but it would be awesome! Perhaps I’ll kick the idea around a bit and if I come up with something useful I’ll create a post on it.

Thanks

Jeff

Jeff

I have to take value of a sale of a publication that contains adverts. It could be published on any day in any month. Our year end is 30th April each year. I then have to spread the income over 730 days (2 years) as that is how long the publication is available for.

Example. Cost is £730 for ease of calculation, column A. Publication date is 10th April 2015, column B. I need 10 days in year one so £10, column C. £365 in the second year as a full year is required, column D. £355 in the 3rd year. My column headers are currently 30th April 2015, 30th April 2016, 30th April 2017.

Each financial year end is a nightmare for me as each sales value is different and published on different days throughout the financial year. I currently work out the daily value, the number of days that should be in each year and then manually do the formulae row by row, column by column. There are hundreds of publications and it is driving me crazy.

Is there a quick way to create a formula for this seemingly simple calculation.

Toni,

Alright my friend, I’ve just posted a new version (Allocation5) to the list above that includes a sheet for yearly allocations…hope it helps!

Thanks

Jeff

Jeff,

Thank you for posting this I have being trying to longest to find this to help converting monthly revenue recognition to monthly. This was so helping for creating in excel. I have then updated this to reflect in PowerPivot.

From a knowledge sharing and high value activity, recreating this document in PowerPivot would be unmeasurable insights and value. I can tell you there is nothing on the internet you can to help with this kind of clarity to build it. Contact me if you need some help. Great job and thanks for sharing your skills to help others be successful this posted was powerful!

Chris – if you have any advice for recreating this in PowerPivot, I’d love to share it with others. If you have instructions, please post, or, if you have a file I can distribute, please email it to me and I can make it available for download.

Thanks

Jeff

Hi Jeff,

Thank you for the post it has been most useful. I am using the monthly allocation but in a few cases I need to have the costs report in specific months that are not linear. For instance, how would I evenly distribute the costs to Feb, May, Aug, Nov?

Thanks

Garrett,

The sample file (Allocation4) has a worksheet named Monthly that demonstrates the formula for an even allocation by month (not based on number of days)…hope it helps!

Thanks

Jeff

Very helpful. Is it possible to set up a formula to distribute a value into months, based on a bell curve distribution.

Brian,

Although I’m unaware of a built-in Excel function that can perform such a calculation with varying months and percentages, one idea is to store the distribution percentages in a Table, for example Month 1 is 5%, Month 2 is 25%, Month 3 is 50%, and so on, and then do a VLOOKUP to retrieve the distribution percent based on the month number.

Hope this lookup idea helps!

Thanks

Jeff

this is great to refer to. do you have a trick to also account for tracking actuals against the forecast and toggling between the two on the month-over-month impact view. example is: project start date, project end date, total contract value, ytd actuals, remaining monthly projected accruals (with actuals hard coded for past months). the logic that i am toying with involves very complicated IF statements: if today is >= project start date, start date for daily amount calculation will equal today instead of project start date. Now to figure out how to make excel do this for me!

But then i also want to go back and see projected versus actuals for past months….

thanks!

Tamara,

Sounds like there are two tasks here. One is to allow your worksheet to toggle between the calculated allocation amounts (forecast) and the actuals (keyed in for past months). The other is to create a variance report on a different worksheet to compute projected versus actuals.

One way to do the toggle part, is to set up a cell that allows you to enter something like 1 for actual and 2 for projected. Then, you could modify the existing projected formula a bit. You leave the part that is working there, and wrap a CHOOSE function around it. If the input cell is 1, then the CHOOSE function will use the existing projected formula. If the input cell is 2 then CHOOSE will use a formula to return the hand-keyed actuals, probably using a lookup function such as VLOOKUP. If you haven’t played with the CHOOSE function, it is perfect for these types of situations, and the following post may help you get started:

http://www.excel-university.com/pick-the-math-with-excel-choose-function/

Regarding the variance report, I would probably set that up on a new worksheet, and then use a lookup or SUMIFS function to populate the values from the projection for the forecast column, and another lookup or SUMIFS function to grab the values from the actuals sheet.

Anyhow, a few ideas to consider…I hope they help!

Thanks

Jeff

Hi Jeff, this is very close to what I need for planning resources by week based on percentage. How can I show the number of hours per week based on the % allocated (100%, 50%, etc).

Thanks for your help.

LeQuisha,

Sure, no problem! I’ve updated the workbook to include a sheet named Weekly Hours that allows you to assign hours by resource. Use the Allocation6 link above to grab a copy of the updated workbook. Hope it helps!

Thanks

Jeff

Can you please send me the sheet that does daily allocations excluding weekends ?

Thanks

Sudhakar

It is available here: http://www.excel-university.com/wp-content/uploads/Allocation1.xlsx

Thanks

Jeff

Hi Jeff, very nice work. However notice that if the monthly project has 2 rows: The first one starts at 1 January to 15 January and the second line starts from 15 January to 31 January , then the total days for January are 32. For the period 1 – 15 January (15 days) and for the period 15-31 January (17 days). Do you have a solution for this?

January 15 is being included twice in the Excel formulas because it is listed on both rows. To use the formulas provided in the workbook, I would recommend updating your second row to January 16 to January 31.

Hope it helps!

Thanks

Jeff

This could be nice , but in my project I take into account the time the project starts and ends.

Thanks

Hi Jeff

I would like to have rent calculated based on total tenure period, in years months and days. However, for rental per day, it is based on the number of days in the specific month itself. Example:

Rental per month: $1550

Tenure start date:5 Mar 2012

Tenure end date: 12 July 2013

Tenure period from 5 Mar 2012 to 12 July 2013 is 1 Year, 4 Months and 8 Days.

Therefore, rental for the tenure period will be $1550 x 12 months + ($1550 x 4 months) + [(8/ (31 days in July)] x $1550 = $25,200

Can Excel actually help to calculate rental in such a way?

Thanks!

Hey Emily

I don’t know of any way you can do that exactly…but you can divide the payment by days and multiply the daily rate by the total days of the tenure.

I hope that helps,

Kurt LeBlanc

Hi Jeff,

The above is very useful. How do you get this formula to start and stop at a specific end date? So if I have a start date of 3rd July and an end date on the 15th September, how can I get the formula to only work out from the 3rd July up until the 15th September? Ultimately, I need the formula to start at a specific date and end at a specific date, rather than just the start and the end of the month. Is this possible?

Thanks.

Curtis

Jeff,

Managed to sort it out.

Thanks again.

Curtis

Hi

OK, this would be useful if it did what it says on the tin!

Click:

Alloc = Alloc

Alloc 2 = Alloc 1

Alloc 3 = Alloc 2

Alloc 4 = Alloc 3

Alloc 5 = Alloc 5

Alloc 6 = Alloc 6

So what happened to Alloc 4?

Also if Alloc 3 is supposed to give a daily Alloc, between 2 dates, why does it give a monthly amount?

I need an amount per WORKING day, between 2 dates, please.

Hi Philip,

I’m sorry, but could you try to clarify your questions? I’m having trouble understanding. NETWORKDAYS() finds the working days between two dates though.

Kurt LeBlanc

I have an activity start and end date need to calculate a number of days each month the activity has Run for eg below is the table

Start Date End Date Months MonthlyAmt 30-04-15 31-05-15 30-06-15 31-07-15

16-05-14 15-05-15 12 3,125.00

16-05-14 15-05-15 12 3,125.00

14-03-15 11-06-15 3 12,240.00

Hey Siddhanth

Thanks for the idea! Mr. Jeff will consider this for another blog.

Kurt LeBlanc

333661 639265Just what I was seeking for, appreciate it for posting . 985505

You should use Networkdays(A1,B1, C1:C5) – where A1 is your start date and B1 your end date and C1:C5 is a list of Bank Holidays, where no work will be undertaken.

I use this to get the number of days between the dates, then use a formula which gives me the Daily Cost, which I just copy right under each date. This gives me a daily spend, then I can sum into weeks or months or quarters.

Hey Philip

I really appreciate the advice! Can you point me to the file and sheet you used this on so I can understand it better?

Thanks,

Kurt LeBlanc

It is a file I created, however I do not know how to upload it.

please email me at philiphales@blueyonder.co.uk

Regards Philip

Hey Philip,

I’m sorry I didn’t write this: my email is kurleb10@gmail.com

I hope I can help:)

Kurt LeBlanc

Hi Jeff,

Is there a way you can apply this to weeks?

In that I have formulated to so costs are shown in the relevant months (thank you for your explanation), but I can’t figure out how to split costs into the relevant weeks of the year, i.e. week 3 week 4 = January – week 5 week 6 = February

Hi Kyle

Mr. Jeff has updated his allocation file several times. I believe you’d be most interested in Allocation2:)

Let me know if that isn’t what you are looking for and I’ll be happy to help!

Kurt LeBlanc

Hi Kurt,,

thank you for your prompt reply, to answer you Allocation 2 works and does not, in that it has added a another analysis dimension but it hasn’t fully tackled my main problem.

Here is the information I have and how I want to split it: dd/mm/yy and this with week 1 ending 03/01/16 with subsequent weeks +7days

Startdate Enddate Amount Week 1 Week 2 Week 3… January

05/01/16 12/01/16 £1000.00 750 250 1000

I hope from this you can help, at the moment I am running a =sumif() on the daily amount to found if the day is in the week number however I would like this sheet to be an independent analysis.

Thanks,

Kyle

Hi Kyle,

I’m sorry, but could you send your file to kurleb10@gmail.com? I need to see the data table and just explain how you want it to look.

Thanks,

Kurt LeBlanc

Hi jeff,

That was awesome what u have worked, can you please let us know if it is possible for the weekly basis of allocation of quantity….. cheers…… i am from construction department

Thanks,

Faizal. K

ksmdfaizal@gmail.com

Hi Jeff,

Would it be possible to adapt your model to work with values that need to be distributed across quarters? For example if I had an amount of $12000. The Alloc Start = 01/02/2016. The Alloc End = 31/01/2017. I would like to see the allocation of $3000 in Feb 2016, May 2016, Aug 2016, and Nov 2016.

Thanks in anticipation.

Mark

To update the workbook to do quarters, open the Monthly worksheet and then change the EOMONTH functions in the monthly header row to use 3 instead of 1 for the second argument. Hope this helps, post back as needed.

Hi Jeff – thank you for posting. Question – on the monthly allocation version how would you go about adding prorated months?

example: contract is Jan 20 2016 – Jan 19 2017 I need to spread the monthly amounts evenly over months Feb 2016 – Dec 2016, but the first month (Jan 2016) only gets 12 days (12 of 31) of revenue and the last month (Jan 2017) would get 19 days (19 of 31).

Thanks!

The first worksheet in the workbook does indeed allocate into monthly columns based on the number of days. Let me know if that one works?

Jeff – thanks for the reply. It looks like that that one allocates the daily amount based on the number of days in the month.

I was looking to prorate a flat monthly fee evenly – so even though Feb has 28 days it still gets the same revenue as Mar.

In the example above assume $100 monthly fee. Jan 2016 would get $38.70 (12/31 x 100), Feb – Dec 2016 would each get $100, and then Jan 2017 would get $61.30 (19/31 x 100).

Am I missing it?

Oh, sorry about that, I misunderstood. You are right. Let me play with the formula a bit and see if I can get something working for that.

hello, im trying to use this formula in a sheet i have for nights reservation (like a hotel reservation sheet) but it is not giving me the right amounts per month. for example, i have a reservation from 4/29/16 to 5/2/16 and the formula is giving me just 1 night for april (which cant be, because it is April 29 and April 31st) and 2 night for May (which is only 1. how can i do to make the formula get it from april 29?

thank you very much!!!

Hey Marjorie

Can you tell me what you are doing for your process to retrieve the values? I can help you better that way:)

Thank you

Kurt LeBlanc