Calculate pro rata based on start/end date

Hi everybody,

I do not get my head around how I could solve the following via Excel and was hoping you could help. I would like to calculate the pro rata value of an amount based on start date and end date for the impacted year.

Example: I get an invoice of 1000 USD which covers the period Oct 2017 to February 2018 (5 months). Excel shall now decompose the 1000 USD and assign the appropriate value to 2017 (3 months = 600 USD) and 2018 (2 months = 400 USD).

Note: The date range and values are flexible.

Any ideas how I could do that? Thanks in advance.

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Messier than you'd think ...

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
10							Year
11	Item	Start	End	Months	Amt		2016	2017	2018	2019	2020	2021	2022
12	Water	2017-04-01	2017-11-06	8	160		0	160	0	0	0	0	0		E12: =DATEDIF(EOMONTH($C12, -1) + 1, EOMONTH($D12, 0) + 1, "m")
13	Heating	2017-10-01	2018-03-31	6	1800		0	900	900	0	0	0	0		H12: =$F12 / $E12 * IFERROR(DATEDIF(MAX(EOMONTH($C12, -1) + 1, DATE(H$11, 1, 1)), MIN(EOMONTH($D12, 0) + 1, DATE(H$11, 13, 1)), "m"), 0)
14	Tax	2018-01-01	2019-03-30	15	1500		0	0	1200	300	0	0	0
15	Insurance	2018-04-01	2018-12-31	9	560		0	0	560	0	0	0	0

You're welcome.

question : is it 91+90=181 or 92+89=181 for both years ?

=MAX(0,MIN(DATE(Table2[[#Headers],[2018]],1,1),$C3)-MAX(DATE(Table2[[#Headers],[2017]],1,1),$B3))