Trying to distribute Revenue from different clients across various months and years

Occasional Contributor

Trying to build a single formula that can distribute Revenue from different clients across various monthly contract durations> Would also like to use to formula for one-time service fees as well.

 

Currently working on this formula but having issues with:

-Contract not extending/ending the whole duration. Row 1 should include May 2022 but ends 1 month short

- Rows 12 & 14 when it is a single one-time contract. 

-Row 4 should also total to $53,365 not $55,589 (which is adding another period or $2,224)

 

** Denotes formula

Cell H2: =IF(AND(H$1<=$F19,H$1>=$E19),($D19/(ROUNDDOWN(($F19-$E19)/30,0))),"")

 

 DEFHI                                           
1Contract ValueStart DateEnd Date01/31/2019Feb-19 **[(=EOMONTH(H1,1)]Mar-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-22
2$76,25005/23/1905/23/22**   2118.055562118.0555562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.055562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.0555562118.055556     
3$97,50010/21/2110/21/22                                 812581258125812581258125812581258125812581258125
4$53,36501/31/1901/31/212223.5520832223.552082223.5520832223.552082223.552082223.5520832223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.552082223.5520832223.5520832223.5520832223.5520832223.552083                    
5$49,02008/20/2008/20/23                   1361.666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.6666671361.666667
6$42,52501/30/1901/30/221181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.251181.25         
7$38,25002/25/2102/25/24                         1062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.51062.5
8$33,22510/25/2110/25/23                                 1384.3751384.3751384.3751384.3751384.3751384.3751384.3751384.3751384.3751384.3751384.3751384.375
9$17,50007/01/1907/01/20      1458.333331458.333331458.333331458.333331458.333331458.333331458.333331458.333331458.333331458.333331458.333331458.33333                           
10$42,50007/27/1907/27/20      3541.666673541.666673541.666673541.666673541.666673541.666673541.666673541.666673541.666673541.666673541.666673541.66667                           
11$1,143,75007/11/2107/11/22                              95312.595312.595312.595312.595312.595312.595312.595312.595312.595312.595312.595312.5   
12$75,00007/11/21NA                              #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
13$945,00006/01/2106/01/22                             787507875078750787507875078750787507875078750787507875078750    
14$60,00006/01/21NA                             #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
15$675,00009/06/1909/06/22        187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750187501875018750 
16$529,65010/24/2110/24/22                                 44137.544137.544137.544137.544137.544137.544137.544137.544137.544137.544137.544137.5

 

 

Trying to create something like this with various start and end dates going from dd/mm/yyyy to mm/yyyy

https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png

0 Replies