Prorating savings based on number of months in a quarter

[Obainoluciano]

Hi All
i need with a formula to do the following calculation
1 determine the quarter that a date falls in and then compare it to the last quarter of the year.
2. Determine the number of months in each quarter and then prorate an amount based on the number of month in each quarter.

for example, if I have a date of June 5, the month falls into quarter 2 and when compared to the end of the year, I have 3 quarters - quarter 2 ( 1 month), quarter 3(3 months) and quarter 4 ( 3 month). I would like to pro-rate an amount ($1245) into 3 different columns based on the number of months in that quarter.

 

[sheetspread]

But June has 7 months remaining, or 3 quarters inclusive. Which do you want? Here's quarters:

Excel 2010
	A	B	C	D	E
1	6/5/2018	1245	415	415	415
Sheet6

Cell Formulas
Range	Formula
C1	=IF(COLUMN(A1)>1+INT((2+MONTH($A1))/3),"",$B1/(1+INT((2+MONTH($A1))/3)))

oh wait I see, hang on.......

 

[Obainoluciano]

But June has 7 months remaining, or 3 quarters inclusive. Which do you want? Here's quarters:

Excel 2010

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]6/5/2018[/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"]415[/TD]
[TD="align: right"]415[/TD]
[TD="align: right"]415[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(COLUMN(A1)>1+INT((2+MONTH($A1))/3),"",$B1/(1+INT((2+MONTH($A1))/3)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



oh wait I see, hang on.......

of the 7 months between june to december, i would want column C to be 1/7*1245 =177.86, column D = (3/7)*1245 =533.57 and column E = (3/7)*1245 =533.57. Column C label will be Q2, D - Q3 and E-Q4

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E
1	6/5/2018	1245	177.8571	533.5714	533.5714
Sheet6

Cell Formulas
Range	Formula
C1	=IF(COLUMN(A1)=1,(3-MOD(MONTH($A1)-1,3))*($B1/(13-MONTH(A1))),IF(COLUMN(A1)>5-(INT((MONTH($A1)+2)/3)),"",3*$B1/(13-MONTH($A1))))

 

[Obainoluciano]

Yes, this is what i have in mind.

Thanks for the help