Calculate how many months in 2016

[Nordin1980]

Hi All,

I have this dilemma I have a excel list with amounts that have a revenue recognition over a 12 month period, now I want to know how I can calculate only the amount that is relate to 2016. The Rev Rec start date is the date the revenue recognition starts and the Rev Rec end date is the date it ends. How can I create a formula that takes the total amount divides it over the total period and returns only that portion that is related to 2016?

Can anyone please help because I am stuck now and if I have to do this manually it will take me ages because the file contains more than 35K lines. (I have attached a small portion of the file as a example).

Thanks for your help in advance,

Kind regards,
Nordin

[FlameRetired]

Try this formula in I2 and fill down.

Please Login or Register  to view this content.

[Nordin1980]

Try this formula in I2 and fill down.

Please Login or Register  to view this content.

Thanks Dave,

Worked perfect one other question, I forgot to mention that I also need the amount of months is 2016?

Can you please let me know how I can work that out?

Thanks in advance,
Nordin

[alansidman]

In P1 1/1/2016
In I2 =DATEDIF(G2,H2,"m")+1 and copy down
In J2 =DATEDIF($P$1,H2,"m")+1 and copy down
in K2 =(E2/I2)*J2 and copy down

v	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Internal ID	Date	Type	Document Number	Amount	Department	Rev. Rec. Start Date	Rev. Rec. End Date	Length	Balance Months	Revenue					1/1/2016
2	450862	10/1/2014	Invoice	55056	360.00	Membership	7/1/2015	6/30/2016	12	6	180
3	470362	11/18/2014	Invoice	55729	360.00	Membership	2/1/2015	1/31/2016	12	1	30
4	470363	11/18/2014	Invoice	55730	360.00	Membership	2/1/2015	1/31/2016	12	1	30
5	470364	11/18/2014	Invoice	55731	360.00	Membership	2/1/2015	1/31/2016	12	1	30
6	470365	11/18/2014	Invoice	55732	360.00	Membership	2/1/2015	1/31/2016	12	1	30
7	470366	11/18/2014	Invoice	55733	360.00	Membership	2/1/2015	1/31/2016	12	1	30

[FlameRetired]

I forgot to mention that I also need the amount of months is 2016?

Check alansidman's formula for I2.