Need help with formula to defer contract by month

[aka189]

Hi All,

Can you please help me with attached file? What I'm trying to do is split contract by month as the start date and end date is given. I also managed to count days in month on row 2 to calculate amount per month.

What I'm struggling is the formula that will see the end date and making sure total doesn't exceed the sale amount. Basically my formula just calculates sale every month in row 3. It should look like row 4 but don't know how can I do a formula. Also I don't know how to make it work from exact start date also so it calculates in right month also as can be seen in row 6 (incorrect row 5).

Can someone please help me?

Thanks

[aka189]

Can someone please help?

[MarvinP]

Hey aka,

See if this is close to what you need.

[FDibbins]

Try this, copied down and across...
=IF(AND($B3<=E$2,$C3>=E$2),$A3/$D3*E$1,"")

Also, please note that forum rules require you to wait at least a day before "bumping" your thread

[aka189]

Hi Both thanks for your help.

Just one problem that I can see. It works fine if start date is 1st and end date is 31st of the month but if start date is mid month and end date is also mid month as in row 4 it still counts full month amount as opposed to allocating reduced day amounts.Payments between Start and End dates.xlsx

[aka189]

Can anyone please look at the attached spreadsheet. I managed to find some formula that calculates value each month to carry forward. What I would like to do is find the exact value for that month rather than carry forward value. You can see formula in yellow color row and the result I want in green color row.

Can anyone please have a look?

I really appreciate your help.