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

Get a quick email notice when a new Excel article is available

• This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

## 82 comments:

1. blake bradley
Reply

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

2. Willa
Reply

Awesome resource. Thanks for posting.

3. Danielle
Reply

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.

1. jefflenning Post author
Reply

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!

4. Susannah
Reply

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!

5. Susannah
Reply

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!

1. jefflenning Post author
Reply

Susannah,

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

Thanks
Jeff

6. Susannah
Reply

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

1. jefflenning Post author
Reply

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

Keep me posted….thanks!!

7. Brian
Reply

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!

8. Brian
Reply

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.

1. jefflenning Post author
Reply

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

9. Rajkamal Tiwari
Reply

Excellent file. It helped me a lot.
Thank you very much.

10. GK
Reply

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

1. jefflenning Post author
Reply

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

11. dav
Reply

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!

1. jefflenning Post author
Reply

Glad it helped 🙂
Thanks,
Jeff

12. Dipiksha Dahya
Reply

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

1. jefflenning Post author
Reply

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

13. Moshon
Reply

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

1. jefflenning Post author
Reply

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

14. Toni Bennett
Reply

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.

1. jefflenning Post author
Reply

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

15. Chris Ortega
Reply

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!

1. jefflenning Post author
Reply

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

2. Mohamed Ehsan
Reply

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.

16. Garrett Johnson
Reply

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

1. jefflenning Post author
Reply

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

17. Brian
Reply

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

1. jefflenning Post author
Reply

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

18. Tamara Upton
Reply

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!

1. jefflenning Post author
Reply

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

19. LeQuisha
Reply

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.

1. jefflenning Post author
Reply

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

20. Sudhakar Sivaraj
Reply

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

Thanks

Sudhakar

21. Konstantinos
Reply

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?

1. jefflenning Post author
Reply

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

22. Konstantinos
Reply

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

Thanks

23. Em
Reply

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!

1. Kurt LeBlanc
Reply

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

24. Curtis Morris
Reply

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

1. Curtis Morris
Reply

Jeff,
Managed to sort it out.
Thanks again.
Curtis

25. Philip Hales
Reply

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.

1. Kurt LeBlanc
Reply

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

26. Siddhanth
Reply

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

1. Kurt LeBlanc
Reply

Hey Siddhanth

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

Kurt LeBlanc

27. tUm7C4im
Reply

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

28. Philip Hales
Reply

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.

1. Kurt LeBlanc
Reply

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

29. Kyle
Reply

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

1. Kurt LeBlanc
Reply

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

1. Kyle
Reply

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

30. Faizal. K
Reply

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

31. Mark B
Reply

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

1. jefflenning Post author
Reply

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.

32. Andy
Reply

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!

1. Jeff Lenning Post author
Reply

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?

33. Andy
Reply

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?

1. Jeff Lenning Post author
Reply

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.

1. Help Needed
Reply

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

34. Marjorie
Reply

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

1. Kurt LeBlanc
Reply

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

35. chloe
Reply

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

36. Martha Wachira
Reply

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

37. Manish Gupta
Reply

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.

38. graham marshall
Reply

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

1. Jeff Lenning Post author
Reply

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

39. Jess
Reply

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

1. Jeff Lenning Post author
Reply

Welcome!

40. Joseph
Reply

I’m so grateful, dear Jeff!
I have been looking for a solution for a long time , it helped me a lot.

41. Andy
Reply

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

42. Sumod Mohan
Reply

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

43. Justin
Reply

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.

44. MURALI ANANTUNI
Reply

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

45. Jun Roperos
Reply

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.

46. Wendy
Reply

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

1. Wendy
Reply

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.

47. Riyaz
Reply

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

48. sultan khandokar
Reply

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

49. Rajib Dey
Reply

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.

50. Alice
Reply

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?