# Prorating savings based on number of months in a quarter

#### Obainoluciano

##### New Member
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.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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

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

Last edited:

#### Obainoluciano

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

Excel 2010
ABCDE
16/5/20181245415415415

</tbody>
Sheet6

Worksheet Formulas
CellFormula
C1=IF(COLUMN(A1)>1+INT((2+MONTH(\$A1))/3),"",\$B1/(1+INT((2+MONTH(\$A1))/3)))

</tbody>

<tbody>
</tbody>

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

##### Well-known Member
This?

• Obainoluciano

#### Obainoluciano

##### New Member
Yes, this is what i have in mind.

Thanks for the help