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 :-)

Hi and welcome back to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)