https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png **similar to this** trying to figure out a single formula that will allow me to split up the value from Column A across columns B-Z with different start and end dates Contract ValueStart DateEnd Date Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21$1,100,00009/06/2009/06/23 $50,00009/06/20NA $375,00005/28/2005/28/21 $172,47502/03/2102/03/22 $231,75008/07/1908/07/22 $191,25006/26/2006/26/21 $162,56012/08/2012/08/23 $127,47512/30/2012/30/21 $212,50003/07/2103/07/24 $122,06007/25/2007/25/23 $104,12504/26/1904/26/22

John_Bloggs650 Continued from here:https://techcommunity.microsoft.com/t5/excel/formula-function-to-evenly-distribute-revenue-over-a-variable/m-p/1299311#M58368 Thanks for starting a new thread.Though, you haven't answered all of my questions yet. How would you treat a contract without an end date? And do I understand correctly that you now want to split the amounts into months taking the number of days per month into account? Or just determine that a contract covers 12 months and split it in 12 equal terms?

Riny_van_Eekelen Just started one here: https://techcommunity.microsoft.com/t5/excel/dynamic-formula-that-splits-distributes-revenue-a-value...Im using Office 365. sorry the columns would be A: CA. with defined start and end dates* Have been playing around with this formula but the days in each month are variable so 30 doesn't necessarily work, with the total falling slightly short.=($E2<=H$1)*(H$1<=$F2)*($D2/(($F2-$E2)/30))or this=IF(AND(H$1<=$F2,H$1>=$E2),SUM($D2/SUM(SUM($F2-$E2)+1)),"")Trying to do something similar to this:https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png

John_Bloggs650 No need to copy what's already in the other thread. And you aren't really answering my questions. But perhaps the attached file will help you on your way.

Riny_van_Eekelen I apologize for any confusion.A few things to note:- All contracts have a start and an end date (although each one begins and ends at a different date)- some contracts are longer than others ( 12 months vs 36 months )-I would like the contract to be divided evenly through the start and end date-think I'm getting closer with this formula if you can provide any insight:=IF(AND(H$1<=E2,H$1>=$F2),SUM($D2/SUM(SUM($F2-$E2)+1)),"") Thanks in advance. P.S. Riny_van_Eekelen -on the file that you uploaded the start date in B1 starts 9/6/2020 however the formula does not begin to fill until Column Z or Oct-20 (* a month after)-Also in column D, where was 30.41667 pulled from in the formula? not sure why its not allowing me to upload the file but this is the complete table Contract ValueStart DateEnd Date Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24Nov-24Dec-24$1,100,00009/06/2009/06/23 $50,00009/06/20NA $375,00005/28/2005/28/21 $172,47502/03/2102/03/22 $231,75008/07/1908/07/22 $191,25006/26/2006/26/21 $162,56012/08/2012/08/23 $127,47512/30/2012/30/21 $212,50003/07/2103/07/24 $122,06007/25/2007/25/23 $104,12504/26/1904/26/22 $99,45003/31/2003/31/23 $93,00006/06/2006/06/21 $91,12511/25/2011/25/23 $82,44505/24/2005/24/21 $82,50004/25/2104/25/24 $80,00008/07/2108/07/24 $76,25005/23/1905/23/22 $97,50010/21/2110/21/22 $53,36501/31/1901/31/21 $49,02008/20/2008/20/23 $42,52501/30/1901/30/22 $38,25002/25/2102/25/24 $33,22510/25/2110/25/23 $17,50007/01/1907/01/20

John_Bloggs650 You wrote: "on the file that you uploaded the start date in B1 starts 9/6/2020 however the formula does not begin to fill until Column Z or Oct-20 (* a month after)" Well, that's because you weren't clear. The formula I used splits the contract value into equal terms. If a 12 month contract starts and ends somewhere during a month, either the first or the last month will have to be skipped. Now I understand that your intention is to split it into 13 periods. A partial first month, then 11 whole months and a partial 12th month. But do you also want the 11 whole months to vary based on the number of days in each month? Pasted your formula in my own file (with adjusted references) and it doesn't seem to make any sense. I must be doing something wrong. Can't really test it without seeing it at work in your file. Can you upload it or else share it via OneDrive or similar?

Dynamic formula that splits/distributes revenue a value among various monthly start and end dates

Riny_van_Eekelen
Platinum Contributor
Sep 28, 2022
John_Bloggs650
Continued from here:
https://techcommunity.microsoft.com/t5/excel/formula-function-to-evenly-distribute-revenue-over-a-variable/m-p/1299311#M58368
Thanks for starting a new thread.
Though, you haven't answered all of my questions yet. How would you treat a contract without an end date? And do I understand correctly that you now want to split the amounts into months taking the number of days per month into account? Or just determine that a contract covers 12 months and split it in 12 equal terms?
- John_Bloggs650
  Copper Contributor
  Sep 28, 2022
  Riny_van_Eekelen
  
  Just started one here: https://techcommunity.microsoft.com/t5/excel/dynamic-formula-that-splits-distributes-revenue-a-value...
  
  Im using Office 365. sorry the columns would be A: CA. with defined start and end dates*
  
  Have been playing around with this formula but the days in each month are variable so 30 doesn't necessarily work, with the total falling slightly short.
  =($E2<=H$1)*(H$1<=$F2)*($D2/(($F2-$E2)/30))
  or this
  =IF(AND(H$1<=$F2,H$1>=$E2),SUM($D2/SUM(SUM($F2-$E2)+1)),"")
  
  Trying to do something similar to this:
  https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png
  - Riny_van_Eekelen
    Platinum Contributor
    Sep 28, 2022
    John_Bloggs650 No need to copy what's already in the other thread. And you aren't really answering my questions. But perhaps the attached file will help you on your way.
    
    John_Bloggs650.xlsx13 KB

Forum Discussion

Dynamic formula that splits/distributes revenue a value among various monthly start and end dates

Share

Resources