## Excel Formula to Allocate an Amount into Monthly Columns

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)

### Excel is not what it used to be.

You need the **Excel Proficiency Roadmap** now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

### Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office 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

Hi Jeff, Thanks for the awesome content here! I have a small issue where, the end date is on a weekend, it’s adding one more extra day.. any suggestions?

(This is for the formula excluding weekends using Networkdays).

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 Susannah, Did you figure out the difference? Even I have a similar issue, where the end date is coming on a weekend, it’s adding one more day.

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

Hi Jeff – This is fantastic; thank you for posting! I am wondering if there is a way to write this so that I can say “if the customer number range (A:A on another sheet) matches the unique customer number here (A2), then look for the allocation following the formula? I tried modifying it to read as this: =IF(Data!A:A=Deferrals!A2,IF(Deferrals!B1=MEDIAN(Data!E:E,Data!H:H,B1),Data!T:T,0)), but I get “FALSE” all the way down the sheet. I appreciate any help you can give. Thanks again for posting this – super useful

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

I’ve been scouring the internet for something like this for over a week and I finally found it ! This is exactly what I was looking for. Many many thanks Jeff! Did you get around to this on PowerPivot ?

I need to run these calculations with half a million records in Excel ! I think PowerPivot with DAX functions and some Date Tables might be able to work this out much more efficiently.

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

HiJeff,

I have been struggling with this for ages and came across yours which will almost get me there.

I need to use it to work our FTE costs.

variables: Rates, days per week worked, working days, public holidays, personal holidays (manual inputs to formula) and Financial months (not calender months) Is there anyway your working days formula in Allocation 2 can be used to work our between 2 different dates (9/11 to 6/12 = Dec financial month) so I want it to return 20 days * rate * days per week allocated – public holidays – personal holidays

thanks

Kads

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

This is great! I have data that needs to start and end on different quarters and therefore the output is quarterly. What would be the easiest/clean way to do it?

Thanks!

Hi Jeff,

Thank you so much for the guide above, the sample file attached has helped me a lot on the allocation by days.

However, I was wondering if you could help to formularize for below situation:

Say invoice date was 20/07/2015, invoice amount $1,200, start date was 15/05/2015 and end date was 14/05/2016. Revenue would only start to recognize on 20/07/2015 as a monthly allocation. How should I formularize this? It would be a monthly allocation by dividing to 12 months and revenue recognition that starts in July will be ($1,200/12 months X 3 months represent May to July). Appreciate your expert advice! Thanks

Hey Jeff,

This is helpful. However, my months don’t end on the calendar days. I am using a financial calendar. My month could begin and end in the middle of the month. Do you have any ideas how to calculate the values based on an exact date rather than the end and beginning of a calendar month?

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 am trying to determine the revenue every month based on the payment schedule.

So for example, If i specify that hte payment cycle is 2 months, then every 2 months, It should show a revenue value x ( which i will specify in a seperate cell).

Revenue Value in cell A1 = 500

Payment cycle A2 = 2 months

So Column D E F G H I J = M1 M2 M3 M4 M5 M6 M7

I want M3=500

M6 =500

M8= 500

Is there a formula to do this less painfully.

Sof i change A2 = 1 months,

then M2 = M4 = M6 = 500

Thanks for the formula. I have an issue where in a span of 316 days it loses approximately 2 days. I am calculating widgets per day. I have 150,224 in 316 days which comes out to 475.39 per day. When I do the spread with the formula it comes out to 149,273 which is a delta of 951 or about 2 days. Any thoughts with what I am doing wrong. Formula below.

=$L6*((MAX(BW$4-$O6,0)-MAX(EOMONTH(BW$4,-1)-$O6,0))-((MAX(BW$4-$P6,0)-MAX(EOMONTH(BW$4,-1)-$P6,0))+(EOMONTH(BW$4,0)=EOMONTH($O6,0))))

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

I am trying to allocate amount in quarterly columns using same logic but it is missing out 1 day in some cases. Do you have any sheet build for quarterly allocation..?

Thanks in advance. appreciate your efforts.

Regards,

Manoj.

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

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

[email protected]

This is almost what I need, however

I am doing a cash flow projection. I have monthly columns

I have set equal amounts to be posted to future months columns every x days e.g. a payment of $1000 every 100 days.

Obviously these transactions will appear in some months and not in others.

I want to be able to see the actual cash balance in a specific month, not averaged out over months.

I’m led to believe that the solution lies somewhere in

x=y/5

x-int(x) = z

if(z>0,”no”,”yes”)

but I haven’t had any luck with that

I’ve looked at MOD(), INT(), CEILING() but haven’t been able to get anything to work with these either.

Thanks in advance for taking on the challenge.

Hi Jeff

Is there a way to show it by the closing balance each month and then when it reaches zero the amount starts again.

From your example in row 9 the amount is $1,000 and the first month is $344.44 however in Jan 14 the amount shows $655.56 ($1,000-$344.44) then in Feb 14 $344.44 ($1,000-$344.44-$311.11). As the end date is Mar 14 the amount starts again so April 14 the balance is 670.33 ($1,000-$329.67) and May 14 $329.67 ($1,000-$329.67-$340.66). This continues as far out as you have dates in row 8.

Thanks

Jon

I need a formula that would calculate amounts semi-monthly taking into account partial amounts. Can you assist me with this?

This formula works great for the future allocation but how could I show it to work the same for historical allocation as well? The last piece of the formula for historical equals 1 which tacks on another days worth of allocation.

Thanks,

Hunter

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.

I’m also struggling with the same issue as Andy, any insight or breakthrough?

Jeff – love what you have done here. Is it possible to allocate by 360 days using start/End dates and a variable for current date. I am trying to create a prepaid revenue (income) amortization schedule and sometimes the start date is before the current date, so I want the current month column to reflect what it would be for the current month allocation and the previous months prior to bill date (current date), this should allocated in current month column. I can send you an example if that would help, as I would really appreciate any input you may have as I have been struggling with this for weeks.

Thanks.

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

Hey Jeff,

Great article and formulas.

Sorry if this has already been answered, but I’m trying to find a way to automatically pro-rata my weekly volumes into monthly totals. The weeks are 6 days each (Sunday to Saturday), and I need Excel to sum all of the weeks for example December. However as you know the weeks don’t align with the months, so I’m not sure how I would do this. I have a week commencing 27/11/16, then the next one starting on 4/12/16 – so I need Excel to know that in the first week, there are only 3 days that fall in December.

Sorry if I’ve confused you – maybe I’m making this harder than it is!

Much appreciated.

Jeff,

Thank you so much for this spreadsheet. The monthly allocation is what I have been looking for to use with my prepaid expenses. You’ve put a lot of hard work into these formulas and I would like you to know I appreciate it very, very much.

Is there any way to make the last month of the calculation come out to what is remaining of the original amount in column B. For example…

The amount is $91.63, expensed for 12 months. Your spread sheet shows an even amount each month 7.64 (or 7.63583333 if the decimals are extended to 8 places). The total in column W adds to 91.63, but if you multiply 7.64 x 12 it equals 91.68. The first 11 months are fine at 7.64, but the final month should be 7.59.

Hopefully this will be a simple fix. Please, oh please, let it be a simple fix.

Hello Jeff,

I’m trying to use the above formula to allocate annual payroll budget based on start and end date between quarters.

Please help. Thanks very much

Hi Jeff,

Those formulas are very useful.

I’m using the monthly allocation formulas to generate the revenue and the cost of the leased inventory at work.

I’ve been trying to get the weekly cost / revenue without any succes, the only way I”ve been able to get the information is to use the daily formula and spread it over the week I’m looking for. Which is not very effcient.

I’m looking for a way to generate the weekly allocation without excluding the week-end.

Could you please help ?

Thanks

Hi Jeff,

Firstly, thank you for this wonderful article. Your way of explaining is simply superb and easy to understand.

However, I was stuck at the below condition when trying to modify your logic. Is it possible to distribute the revenue like below?

Ex :

Amount Start Date End Date Oct 16 Nov 16 Dec 16 Jan 17 ………… Oct 17

1000 4-Oct-2016 3-OCt-2017 10.75 83.33 83.33 83.33 72.58

(4 Days) (27 Days)

Thanks,

John

Hi There, this is amazing and had been so helpful.

I have a question about a potential addition. Is it possible to add another two columns to add in “breaks”. so for example if you want allocation 1 to have a 3 month break in the middle and the remainder to be re-allocated out? it would be great if anyone had any thoughts on this? Thanks so much!

Chloe

Dear Jeff

This is very helpful. Just wondering if I get one that factors in intervals of payments. Some costs are paid after 3 months others after 6 months. Thanks

Hi, I want to develop a revenue forecast file. I have many contracts for which we have start date, End Date, Billing Schedule (Like monthly, Quarterly etc…), billing period (Like for monthly from 1st Jan to 31st Jan), Billing Date ( like billing will be done on 1st Jan or 31st Jan) and billing amount. How can I forecast revenue.

Hi – have you developed something similar with Power Bi DAX functions? regards, Graham

Not at this point, but sounds like a cool project 🙂

Thanks

Jeff

Hello Jeff,

If I wanted to stop the allocation as a lump sum remaining to take into the next fiscal year, is there a recommended way to do this?

Hi Jeff,

I’m am trying to do a monthly based on how many splits it will have regardless of years.. as we have a rollover and don’t want to be limited to month dates.

Would like to see what it is meant to be for the month and a remaining balance (blue columns) I am happy to email you and example

columns are

start date / split # / customer details/ 1 2 3 4 5 6 7 8 9 10 11 12 / batch balance/ remaining balance (based on the month we are currently running)

Cannot thank you enough for this!! really, really helped me 🙂

Welcome!

Hello Jeff,

Well done!

I spent nearly two weeks developing an allocated capacity “dashboard” — then this page today that is very similar to what I’m attempting. I accomplish my monthly project hour allocations using:

Quoted mfg Hours: 8869

Manufacturing days: DueDate – StartDate = BuildDays. 10/6/17 – 05/04/18 = 210

The BuildDays are manually allocated across a pseudo calendar…

Oct Nov Dec Jan Feb Mar Apr May

25 30 31 31 28 31 30 4

I have Hrs-Allocated-Mo-Yr columns for each Month of the build time.

Each Hrs-Alloc column has a formula: Estimated Hrs / BuildDays * # of days (of each month):

Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18

1056 1267 1309 1309 1183 1309 1267 169

We’re coming to the MonthlyAmt in a similar fashion; but I need to account for the partial months i.e. 25 days of Oct and 4 days in May — not whole months, as our start dates are not uniform. I’d like to eliminate the pseudo-calendar in my spreadsheet and use a variation of your allocation formula =IF(H$9=MEDIAN($C10,$D10,H$9),$G10,0)

or Distributing All Days Across Months: =MAX(0,(MIN($C17,DATE(YEAR(D$16),MONTH(D$16)+1,0))-MAX($B17,D$16)+1))

Best Regards, Lee

i have a set of numbers in different departments, which i need to allocate among a staff of 13 people. Every few hours the numbers keep on increasing and its difficult to divide the work manually as its time consuming. So I am looking for an easy way to distribute it.

Example:-

Dept names A-Z from A1:A26;

their work numbers from B1:B26, totals to 1300;

Now since we have 13 people, each member should get 100 numbers.

Searching a way we can allocate to 13 people so it shows 1:13 people should do which department

Hi Jeff,

Stumbled into your materials whilst searching for help on formula to allocate an amount into monthly columns. Useful indeed. Thanks. I tried the formula in your Allocation6 – “Yearly” sheet, but didn’t work. My spreadsheet is looks like this;

A B C D E F G H I J

Exp. Revenue | Start Date | EndDate | Total Months | Rev/month | 30/12/18 | 30/12/19 | 30/12/20 | 30/12/21 | 30/12/22

150,000,00 10/10/2018 30/11/2022 50 3,000,000

Would you pls assist me with the formula to allocate revenues across 2018 and 2022. Perhaps you can upload this as Allocation 7 so I can download it.

Thanks

Michael

Hi Jeff, any chance you would be able to give direction on allocating values on a weekly basis? I can’t seem to find a way to replace the EOMONTH formula and still achieve results. Also, would need to ensure it captured any allocations that were less than (didn’t span) a week. Thanks!

I’m so grateful, dear Jeff!

I have been looking for a solution for a long time , it helped me a lot.

Thanks. This is helpful for me to apportion revenue into months for my lodging business. (E.g. someone stays from Aug 4 through Sept 15… and I get the right amounts into Aug and Sept).

Hi Jeff,

The solution is really amazing. The same kind of task I was doing by spending much hours, and the sad part was if the amount or start / end date is changed, it’s finished. I need to do the manual entry again. But here which ever the amount or start date or end date change doesn’t give even a one minute additional job. Simply enter the changed date. That’s all. In seconds the result is there.

It’s really great and I will always thankful to you for such a great solution.

May god bless you always to provide your support to the needy people always

Incredible solution, thank you so much for posting – big help on a sales commission calculator that I’ve been working on. One question I was hoping you’d be able to speak to, I’ve been using the weekday allocation sheet and noticed that for March, June, August, and November, the sheet adds in an extra day’s worth of value. For instance, for 3/1/19 – 3/31/19 if I have an amount of $1,000, then the sheet should allocate $47.62 across 21 days or $1,000. But, it adds in an extra $47.62 to yield a result of $1,047.62. This seems to be tied to the final term, +(EOMONTH(J$5,0)=EOMONTH($D6,0)), but I can’t figure out how to fix it for those 4 months, without affecting the other 8 months, which are rendering correctly. Thanks for any help you can give.

your entire explanation is didn’t work for me but your excel attachment did . you are such a nice guy

thank you so much for both detailed explanation and attachments

Lease amount = 120,000

Lease start = 06-May-2019

Lease expiry = 05-May-2020

Payment = 6 Installments

ANSWER:

Amount = 20,000 per 2 months

1st payment = May 2019

2nd payment = July 2019

3rd payment = Sept 2019

4th payment = Nov 2019

5th payment = Jan 2020

6th payment = Mar 2020

Question:

From Jan 2019 to Dec 2020, I want to know what formula should I use in excel so that the amount 20,000 will show in the correct month? Supposed, I had made a table from Jan 2019 to Dec 2020.

Could you share how you figured this out – I’m trying to do this with fiscal year start and end dates, July 1 to June 30.

Thanks,

Wendy

Nevermind – in looking at the spreadsheet examples further it has a place to put a starting date to customize the year period. Thank you! Exactly what I needed.

Hi,

I would like to distribute a defined amount to be spread in 1 to 25 Rank fields.

Example : Money 1000$

Rank : 1

Rank : 2

Rank : 3

Till

Rank : 25

I need to set money value and a range ie 1 to 25, then i need the 1000$ to be distributed in the columns from 90% till 25% in reverse order. Basically if im earning 1000$ deal out of 25 members business who has played some games and own a ranking from 1st to 25. I want to distribute that money to them from more to less based on ranking..

brilliant , this worked, you saved me so much time , a big thank you

Can you please provide the following allocation formula ?

If i put in a sheet Room No “X” has been sold @ Rs 1000/Night from 01/01/2020 to 05/01/2020 i (e 4 nights) i will get the figures 1000 in each cell dtd 01/01/2020, 02/01/2020, 03/01/2020 & 04/01/2020 against Room No “X” in an another sheet.

How would you use this formula but spread the value over weeks not months?

I have to allocate the money in the weeks it was spent so say it was spent between 03/02/20 and the 11/02/20 i need the value to split and apportion itself on the relative weeks.

In theory i imagine the formula isn’t too far off the one used here but im not sure how to alter it?

Great article Jeff together with valuable commentary from the participants – appreciate it

I have a situation below and after some ideas on what would be a better way to get around it ..

I’m using database to group work into weeks. Have work orders on same dates as well. I need to distribute the hours per week. But then there are 4 conditions 1.the st date can be b4 the wk bin.2 in the wk bin 3. After the wk bin 4. Outside the wk bin

What would be the best method of going about getting total manhrs figure in a wk for that record and also capture the remaining hours for their respective Wk bins as start date doesn’t really define the association to the week bin ? But maybe combination of st date and duration will do together with Weeks Bins ?

Not sure if I have articulated it Ok but I can explain if you not clear.

Would be grateful if you can help

Thanks

Do you have something like this to help with accounting entries? For example if I want to see how to allocate a monthly expense (like store rent) over a non-calendar period, such as 4/4/5, or 13 periods of 4 weeks.

H9=$G9*MAX(0;MIN(H$8;$D9)-MAX($C9;EOMONTH(H$8;-1)+1)+1)

Great resource – thank you.

I thought this was close to what I’m looking for but now not sure – I was trying to convert it into a capacity sheet.

So instead of $1000 over three months I’ve got a project of 175 days effort scheduled to take place over the summer of 2021. Rather than distribute evenly over that time I’m trying to see how long it would take assuming max number of workdays was spent per month and be able to change the number of resources i put on it. I can almost do it using simple formulas but the scheduled date part doesn’t work. Any suggestions would be gratefully received.

Hi Jeff,

Thank you for sharing this formula and your logic behind this.

Would you be able to tweak the function so that it allocates cost to any given time period it falls in? For example, time periods in weeks?

I have tried to change MAX((EOMONTH(D23,-1)-B23),0), so that the EOMNTH is simply the date of the next time period which didn’t workout.

Any suggestions?

Thank you!

Need solution for following

Monthly Amount $ 200/- per month

Start Date 21st April 2021

End Date 28th July 2021

ideally it should bifurcated in following manner : Can someone Please help ?

April 2021 (Prorated for 10 days – From 21st April to 30th April) = 200/30*10 = 66.66

May 2021 = 200 /- (full month) (31 days of Month)

June 2021 = 200/- (full month) (30 days in the month)

July 2021 = (Prorated for 28 days – From 1st June to 28th June) = 200/31*28 = 180.64

Total Amount ( 66.66+200+200+180.64)= 647.30