Formulae for Dates and Period calculations that can auto calculate with drop down

[jordanseling]

Hi,
I am unable to figure out the formulae for auto select start and end date with respect to transactions in the Data sheet at fixed intervals and further drop down in the Summary sheet performs the calculations automatically. I also need to extract the number of months for the specific period/s which gives results in segregating data and identify the number of months paid and unpaid. Thanks!

[ChemistB]

For beginning month (in K2 copied down)

Please Login or Register  to view this content.

For end month, (L2 copied down)

Please Login or Register  to view this content.

I'm not sure what results you are looking for with Months paid and unpaid.

[bebo021999]

See my solution in attachment.

I have recalculated from column E to O, in which Column E (monthly rent)=column F/column M = Receivable/period. You can see E3 = 29.028 instead of 30, because customer 2 has various monthly rent (first 7 months with 25000, the rest with 30000), then month paid = 15.69 months.

[jordanseling]

Thank you so much for making this easier for me. This is what I wanted. For Months Paid and Months Unpaid, I am not sure if it will auto populate but that's fine, I can drag down and get the results. Cheers!

[jordanseling]

That's wonderful bebo and even better of what I had done. Many thanks for your help. Regards.