Forum Discussion
Dynamic formula that splits/distributes revenue a value among various monthly start and end dates
Continued from here:
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?
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.pngJohn_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.
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 Value Start Date End Date Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20 Nov-20 Dec-20 Jan-21 Feb-21 Mar-21 Apr-21 May-21 Jun-21 Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 Dec-21 Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 Apr-24 May-24 Jun-24 Jul-24 Aug-24 Sep-24 Oct-24 Nov-24 Dec-24 $1,100,000 09/06/20 09/06/23 $50,000 09/06/20 NA $375,000 05/28/20 05/28/21 $172,475 02/03/21 02/03/22 $231,750 08/07/19 08/07/22 $191,250 06/26/20 06/26/21 $162,560 12/08/20 12/08/23 $127,475 12/30/20 12/30/21 $212,500 03/07/21 03/07/24 $122,060 07/25/20 07/25/23 $104,125 04/26/19 04/26/22 $99,450 03/31/20 03/31/23 $93,000 06/06/20 06/06/21 $91,125 11/25/20 11/25/23 $82,445 05/24/20 05/24/21 $82,500 04/25/21 04/25/24 $80,000 08/07/21 08/07/24 $76,250 05/23/19 05/23/22 $97,500 10/21/21 10/21/22 $53,365 01/31/19 01/31/21 $49,020 08/20/20 08/20/23 $42,525 01/30/19 01/30/22 $38,250 02/25/21 02/25/24 $33,225 10/25/21 10/25/23 $17,500 07/01/19 07/01/20
