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

Occasional Contributor

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

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))),"")

 D E F H I 1 Contract Value Start Date End Date 01/31/2019 Feb-19 **[(=EOMONTH(H1,1)] 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 2 \$76,250 05/23/19 05/23/22 ** 2118.05556 2118.055556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.05556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 2118.055556 3 \$97,500 10/21/21 10/21/22 8125 8125 8125 8125 8125 8125 8125 8125 8125 8125 8125 8125 4 \$53,365 01/31/19 01/31/21 2223.552083 2223.55208 2223.552083 2223.55208 2223.55208 2223.552083 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.55208 2223.552083 2223.552083 2223.552083 2223.552083 2223.552083 5 \$49,020 08/20/20 08/20/23 1361.66667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 1361.666667 6 \$42,525 01/30/19 01/30/22 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 1181.25 7 \$38,250 02/25/21 02/25/24 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 1062.5 8 \$33,225 10/25/21 10/25/23 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 1384.375 9 \$17,500 07/01/19 07/01/20 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 1458.33333 10 \$42,500 07/27/19 07/27/20 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 3541.66667 11 \$1,143,750 07/11/21 07/11/22 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 95312.5 12 \$75,000 07/11/21 NA #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 13 \$945,000 06/01/21 06/01/22 78750 78750 78750 78750 78750 78750 78750 78750 78750 78750 78750 78750 14 \$60,000 06/01/21 NA #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 15 \$675,000 09/06/19 09/06/22 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 18750 16 \$529,650 10/24/21 10/24/22 44137.5 44137.5 44137.5 44137.5 44137.5 44137.5 44137.5 44137.5 44137.5 44137.5 44137.5 44137.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