Spreading a contract value over the duration of 4 years

G

GH2017

New Member
Joined
Oct 5, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi all, I need to allocate a contract value, using a start date, and duration (number of months) from year 2023 to 2026.

I need a formula to populate the yellow highlighted range in the attached screenshot, and be able to drag it down, to provide the values I manually populated for an example.

Thank you

pic.jpg
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
How is 19 months getting you to 2026 from October 2023?
 
Upvote 0
This has driven me nuts... Hope it helps
PENDING.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
1
2End DateStart DateDurationAmount2023202420252026123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
312/29/202312/29/20231100100.00.00.00.012/29/202300000000000000000000000000000000000000000000000
43/3/202411/3/20235500200.0300.00.00.011/3/202312/3/20231/3/20242/3/20243/3/20240000000000000000000000000000000000000000000
53/3/202510/3/2023181800300.01200.0300.00.010/3/202311/3/202312/3/20231/3/20242/3/20243/3/20244/3/20245/3/20246/3/20247/3/20248/3/20249/3/202410/3/202411/3/202412/3/20241/3/20252/3/20253/3/2025000000000000000000000000000000
64/3/202510/3/2023191800284.21136.8378.90.010/3/202311/3/202312/3/20231/3/20242/3/20243/3/20244/3/20245/3/20246/3/20247/3/20248/3/20249/3/202410/3/202411/3/202412/3/20241/3/20252/3/20253/3/20254/3/202500000000000000000000000000000
Sheet1
Cell Formulas
RangeFormula
F2:H2,L2:BF2F2=E2+1
E3:H6E3=SUMPRODUCT(--(YEAR($K3:$BF3)=E$2))*($D3/$C3)
K3:BF6K3=IF(DATE(YEAR($B3),MONTH($B3)+K$2-1,DAY($B3))<=$A3,DATE(YEAR($B3),MONTH($B3)+K$2-1,DAY($B3)),"0")
A3:A6A3=EDATE(B3,C3-1)
 
Upvote 0
Solution
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to allocate contract value over 4 years
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Armstrong_N14 said:
This has driven me nuts... Hope it helps
PENDING.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
1
2End DateStart DateDurationAmount2023202420252026123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
312/29/202312/29/20231100100.00.00.00.012/29/202300000000000000000000000000000000000000000000000
43/3/202411/3/20235500200.0300.00.00.011/3/202312/3/20231/3/20242/3/20243/3/20240000000000000000000000000000000000000000000
53/3/202510/3/2023181800300.01200.0300.00.010/3/202311/3/202312/3/20231/3/20242/3/20243/3/20244/3/20245/3/20246/3/20247/3/20248/3/20249/3/202410/3/202411/3/202412/3/20241/3/20252/3/20253/3/2025000000000000000000000000000000
64/3/202510/3/2023191800284.21136.8378.90.010/3/202311/3/202312/3/20231/3/20242/3/20243/3/20244/3/20245/3/20246/3/20247/3/20248/3/20249/3/202410/3/202411/3/202412/3/20241/3/20252/3/20253/3/20254/3/202500000000000000000000000000000
Sheet1
Cell Formulas
RangeFormula
F2:H2,L2:BF2F2=E2+1
E3:H6E3=SUMPRODUCT(--(YEAR($K3:$BF3)=E$2))*($D3/$C3)
K3:BF6K3=IF(DATE(YEAR($B3),MONTH($B3)+K$2-1,DAY($B3))<=$A3,DATE(YEAR($B3),MONTH($B3)+K$2-1,DAY($B3)),"0")
A3:A6A3=EDATE(B3,C3-1)
Click to expand...
This will work - THANNK YOU!!! Struggled with this one as well.
 
Upvote 0
GH2017 said:
This will work - THANNK YOU!!! Struggled with this one as well.
Click to expand...
I know you have a solution, but I started working on this yesterday too and I was determined to find a solution without helper columns, and I'm still not sure how I got it, but see if this works for you too if you want something in a single formula. Also, when you enter this for the first time, Excel might change the cell formatting to Date, so just change it back to number/general if it does.

Book1 10-5-2023.xlsx
HIJKLMNOP
4Start DateDurationAmount2023202420252026
512/23/20231100100000100
611/3/2023550020030000500
710/3/2023181800300120030001800
810/3/2023191800300120040001900
Sheet1
Cell Formulas
RangeFormula
K5:N8K5=($J$5/$I$5)*LET(n,DATEDIF(EOMONTH($H5,-1),DATE(K$4:K$4,12,31),"m"),MAX(IF(SUM(n)<=$I5,IF(n>=12,12,n),IF($I5-n+12>$I5,0,$I5-n+12)),0))
P5:P8P5=SUM(K5:N5)
 
Upvote 0
dreid1011 said:
I know you have a solution, but I started working on this yesterday too and I was determined to find a solution without helper columns, and I'm still not sure how I got it, but see if this works for you too if you want something in a single formula. Also, when you enter this for the first time, Excel might change the cell formatting to Date, so just change it back to number/general if it does.

Book1 10-5-2023.xlsx
HIJKLMNOP
4Start DateDurationAmount2023202420252026
512/23/20231100100000100
611/3/2023550020030000500
710/3/2023181800300120030001800
810/3/2023191800300120040001900
Sheet1
Cell Formulas
RangeFormula
K5:N8K5=($J$5/$I$5)*LET(n,DATEDIF(EOMONTH($H5,-1),DATE(K$4:K$4,12,31),"m"),MAX(IF(SUM(n)<=$I5,IF(n>=12,12,n),IF($I5-n+12>$I5,0,$I5-n+12)),0))
P5:P8P5=SUM(K5:N5)
Click to expand...
Oops, slight adjustment to my formula. I had a range locked that shouldn't have been.

Book1 10-5-2023.xlsx
HIJKLMNOP
4Start DateDurationAmount2023202420252026
512/23/20231100100000100
611/3/2023550020030000500
710/3/2023181800300120030001800
810/3/2023191800284113737901800
Sheet1
Cell Formulas
RangeFormula
K5:N8K5=($J5/$I5)*LET(n,DATEDIF(EOMONTH($H5,-1),DATE(K$4:K$4,12,31),"m"),MAX(IF(SUM(n)<=$I5,IF(n>=12,12,n),IF($I5-n+12>$I5,0,$I5-n+12)),0))
P5:P8P5=SUM(K5:N5)
 
Upvote 0
dreid1011 said:
Oops, slight adjustment to my formula. I had a range locked that shouldn't have been.

Book1 10-5-2023.xlsx
HIJKLMNOP
4Start DateDurationAmount2023202420252026
512/23/20231100100000100
611/3/2023550020030000500
710/3/2023181800300120030001800
810/3/2023191800284113737901800
Sheet1
Cell Formulas
RangeFormula
K5:N8K5=($J5/$I5)*LET(n,DATEDIF(EOMONTH($H5,-1),DATE(K$4:K$4,12,31),"m"),MAX(IF(SUM(n)<=$I5,IF(n>=12,12,n),IF($I5-n+12>$I5,0,$I5-n+12)),0))
P5:P8P5=SUM(K5:N5)
Click to expand...
Sorry, cleaning up a few more unnecessary bits of the formula:

Excel Formula: 
=($J5/$I5)*LET(n,DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m"),MAX(IF(n<=$I5,IF(n>=12,12,n),IF($I5-n+12>$I5,0,$I5-n+12)),0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Multiple Conditions
2 3
Replies
22
Views
803
Legacy 143009
L
C
  • Question
Revenue Allocation by Week
Replies
3
Views
276
ChetanPuri
C
S
  • Question
How many months a contract is valid for within a fiscal year?
Replies
1
Views
143
Scotty2a
S
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
S
  • Question
Split Dates Between Contract and Reporting Periods
Replies
0
Views
329
*shudder*
S

Forum statistics

Threads
1,215,480
Messages
6,125,050
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top