Split number of months from years

Hi guys, I am new to the forum, and this is my first post.

I get rent from my tenants, monthly. But, this does not happen regularly, ending up with arrears due to me.

I have the current date in B1. [=today(0)

When I receive a rent, I enter the date it in B2.

I need to calculate the number of months rent is due by, say in B4.

I can calculate this fairly easily in one of 2 ways:

1) =DATEDIF(B2,B1,"m") or

2) =(YEAR(B1)-YEAR(B2))*12+MONTH(B1)-MONTH(B2)

This give me the total number of months in arrears.

Now, I want to split up these months as arrears in preceding year (2016) and arrears this year (2017).

For example, if the last rent received is for October 2016, I would like to get a result of 2 and 7, as of July 2017.

To make matters slightly more complex, I often get rent in advance (wish that happened more often!). In this case, the DATEDIF formula goes for a toss, though the 2nd option works well. In this case, suppose I get paid upto, say, December 2017, I would like the results to be 0 and -5.

Is there a way to achieve this?

Many thanks and regards,

Anand Dhuru

Thanks for the response.

I am attaching the file as suggested.

Siva, thanks, this works very well as long as the year in B2 is less than the current year in B1.

But, if Year(B1) is equal to Year(B2), the formula

=datedif(max(edate(b2,0)+1,date(2016,1,1)),min(eomonth(b1,1),date(2017,1,1)),"m" gives an error, and

the next formula

b12=datedif(max(edate(b2,0)+1,date(2017,1,1)),min(eomonth(b1,1),date(2018,1,1)),"m") shows 1 more than the actual value.

Is there a way around this?

Best Regards,

Anand Dhuru

Dave, thanks for that pointer.

I'm sure I'm being incredibly dense, but I somehow cannot get the Ctrl-Shift-Return to work; there seems to be no change to the active cell.

What am I doing wrong?

Best Regards,

Anand Dhuru

Siva, you are a life saver!

Thanks, that works like a champ.

Now, can I be a bit ambitious, and expect the last cell (2017) to show a negative number if I get paid in advance? Currently, it just shows a zero.

For example, if I'm paid upto Dec 17, can that cell show -5?

No matter if its too hassly, I can use your formula the way it is. But if it can be done, that would be perfection itself.

Again, thanks, and regards.

Anand Dhuru