# Pro rata question

#### Cossie

##### Active Member
I am looking for a way to calculate monthly and annual pro rata so that it automatically adjusts to the current day and month and year

eg September has 30 days and on the 15th day of the month the prorata is 50%, 10th day of september is 33% etc but when it changes to october i would like it to calcualte based on 31 days etc

for the annual pro rata i am looking for the same thing so that it calcualtes in any given year the prorata of the current date.

Is a variation of networkdays the answer?

hope this makes sense and thank you

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### sheetspread

##### Well-known Member
This?

Excel 2010
ABC
1Datemonthyear
22/25/201989.29%15.34%
33/9/201929.03%18.63%
43/25/201980.65%23.01%
55/1/20193.23%33.15%
66/1/20193.33%41.64%
77/7/201922.58%51.51%
88/11/201935.48%61.10%
99/22/201973.33%72.60%
1010/3/20199.68%75.62%
1111/5/201916.67%84.66%
1211/7/201923.33%85.21%
1311/11/201936.67%86.30%
1411/18/201960.00%88.22%
1512/17/201954.84%96.16%
Sheet3
Cell Formulas
RangeFormula
B2=DAY(A2)/DAY(EOMONTH(A2,0))
C2=YEARFRAC(DATE(YEAR(A2),1,1)-1,A2,1)

#### Cossie

##### Active Member
Thank you very much

#### Cossie

##### Active Member
if i get this as the result how do i remove the training numbers and end up with 92.8%

=CONCATENATE("Pro Rata ",(\$K\$2*100)) is my formula

Pro Rata 92.8571428571429 is what is displayed

thanks again

#### sheetspread

##### Well-known Member
=CONCATENATE("Pro Rata ",(trunc(\$K\$2*100,1)),"%")

you can also join strings with & instead of concatenate

Last edited:

#### Cossie

##### Active Member
thanks very much again

Replies
3
Views
627
Replies
4
Views
580
Replies
3
Views
1K
Replies
0
Views
564
Replies
2
Views
935

Threads
1,195,952
Messages
6,012,509
Members
441,703
Latest member
clivelincoln

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

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