Forum Discussion
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
No RepliesBe the first to reply
