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.

20130926a

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.

20130926f

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.

20130926i

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.

20130926j

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.

20130926k

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.

20130926l

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.

20130926m

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.

20130926n

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.

20130926o

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)

This article was written by Jeff Lenning

65 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

  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

        1. Kurt LeBlanc
          Reply

          Hey Philip,

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

          I hope I can help:)
          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

        1. Kurt LeBlanc
          Reply

          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

  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.

  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

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.