HELP CALCULATING PRO RATA AMOUNT BETWEEN DATE RANGES

[B3ady]

Hey all,

Looking for some help with the below data set.

I'm looking to calculate a pro rata amount for the impression delivery in Column C, showing what it would deliver between the date ranges in Rows 1 and 2, using the start and end dates in Columns A and B. Have marked an x where the data would go for the example dates

This is assuming the impressions would be served at an equal daily delivery.

Any help would be greatly appreciated!

Cheers!

 

[jtakw]

Hi,

Assuming the days for ranges are Not End Date inclusive (e.g. 4/21/2022 to 5/4/2022 is 13 days), otherwise formula can be adjusted to suit.
Also assuming you Don't want decimal/fraction results, otherwise Remove or Change the ROUND function to suit:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1				4/11/2022	4/18/2022	4/25/2022	5/2/2022	5/9/2022	5/16/2022
2				4/17/2022	4/24/2022	5/1/2022	5/8/2022	5/15/2022	5/22/2022
3
4	4/21/2022	5/4/2022	945609	0	290957	509174	145478	0	0
5	4/25/2022	5/4/2022	3284237	0	0	2554407	729830	0	0
6	4/25/2022	5/4/2022	4234234	0	0	3293293	940941	0	0
Sheet1094

Cell Formulas
Range	Formula
D4:I6	D4	=ROUND(MAX(0,MIN(E$1,$B4)-MAX($A4,D$1))*$C4/($B4-$A4),0)