Formula to correctly allocate monthly tuition fees between 2 months

Hi all,

I am one of the managers of a small language school in Vietnam. I am trying to modify our current student payment excel sheet so that every time a student pays the monthly fee, this is distributed correctly among the months the student is paying for.

I'll give you an example: Student A has started his English classes on the 15th of September (classes run on Tuesday and Thursdays), so he is required to pay the fees every 4 weeks. On September the 15th, he pays for the first 4 weeks, which will have him covered until the 8th of October. When I input the student, fees paid, and date of payment, I would like for these to be distributed between a column that lists income for September, and one for October since he is paying for classes occurring on the 6th and 8th of October.

Basically the formula should look at the date the fees have been paid, calculate how many Tuesdays and Thursdays are in September and October (until the 8th), and then distribute the fees accordingly between the two months.

I've attached a simplified mock payment sheet to clarify this,hope that helps! sample.xlsx

Thanks :-)