Forum Discussion

Copper Contributor

Sep 28, 2022

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

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 d...

excel

Formulas and Functions

John_Bloggs650

Copper Contributor

Sep 28, 2022

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 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

$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

Riny_van_Eekelen

Platinum Contributor

Sep 28, 2022

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?

John_Bloggs650

Copper Contributor

Sep 28, 2022

Riny_van_Eekelen

Hi to Answer your question even if the date encompasses 1 day out of the month, I would still like to include and round it up to the whole month and spread it across evenly.

this is the table Ive been trying to do the transfer test from, Column, 4 Row 2 formula: =IF(AND(D$10<=$B11,D$10>=$A11),SUM($C11/(SUM($B11-$A11)+1)),"")

Start	Finish	Value	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	1-Mar	2-Mar	Mar-22	Mar-22
24-Feb	26-Feb	15.57	5.190	5.190	5.190
25-Feb	28-Feb	26.37		6.593	6.593	6.593	6.593
26-Feb	27-Feb	10			5.000	5.000
27-Feb	1-Mar	30.15				10.050	10.050	10.050
28-Feb	2-Mar	40.66					13.553	13.553	13.553
Feb-29	1-Mar	7.5

Is there a way to pull only the month from the various dates in the first 2 columns that include individual days to use across the other columns as just months?

Please see attached

Data:

https://docs.google.com/spreadsheets/d/1XpKT2NJCe0S8d9hbCcMlB1KwkHJtvi05/edit?usp=sharing&ouid=113886818105478529954&rtpof=true&sd=true

Example

https://docs.google.com/spreadsheets/d/1gN7PZASv7hUj4y-5bdnAP9KuK1XPMq4v/edit?usp=sharing&ouid=113886818105478529954&rtpof=true&sd=true

Riny_van_Eekelen
Platinum Contributor
Sep 28, 2022
John_Bloggs650 Now you have lost me. In your example you split by day.
- John_Bloggs650
  Copper Contributor
  Sep 28, 2022
  sorry for that. Only using it as a rough example. Ideally I would like to use months but could not quite manipulate it