Loan Days Calculation

[Acekay]

Hi
Below is the table where i need the number of days to be calculated. Its basically loan days to be calculated. I have given the values manually but requesting all if anyone can give me common formula to put in cell to give me this value (counting of days).


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31/07/2018[/TD]
[TD]31/08/2018[/TD]
[TD]30/9/2018[/TD]
[TD]31/10/2018[/TD]
[TD]30/11/2018[/TD]
[TD]31/12/2018[/TD]
[TD]31/01/2019[/TD]
[TD]28/02/2019[/TD]
[/TR]
[TR]
[TD]Loan Date[/TD]
[TD]Payment Date[/TD]
[TD]Due Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/06/2018[/TD]
[TD]25/06/2018[/TD]
[TD]30/08/2018[/TD]
[TD]0[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18/06/2018[/TD]
[TD]18/07/2018[/TD]
[TD]16/09/2018[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01/08/2018[/TD]
[TD]13/09/2018[/TD]
[TD]30/10/2018[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01/08/2018[/TD]
[TD]25/10/2018[/TD]
[TD]30/10/2018[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]05/09/2018[/TD]
[TD]22/11/2018[/TD]
[TD]04/12/2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]31[/TD]
[TD]22[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/10/2018[/TD]
[TD]10/01/2019[/TD]
[TD]06/01/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]23[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18/10/2018[/TD]
[TD]25/02/2019[/TD]
[TD]16/01/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

Thanking you in advance.
****** id="cke_pastebin" style="position: absolute; top: 25px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]MONTH END[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[footoo]

Enter in D2 and drag across and down :

=MAX(MIN(D$2,$B4)-MAX(EOMONTH(D$2,-1),$A4),0)

 

[Acekay]

Thanx a lot. Its working perfectly.

 

[Acekay]

Hi,
What if there is no payment date mention in the table? and want to calculate days till now ...eg under first raw if we change the data as;


[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[TD]MONTH END[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31/07/2018[/TD]
[TD]31/08/2018[/TD]
[TD]30/9/2018[/TD]
[TD]31/10/2018[/TD]
[TD]30/11/2018[/TD]
[TD]31/12/2018[/TD]
[TD]31/01/2019[/TD]
[TD]28/02/2019[/TD]
[/TR]
[TR]
[TD]Loan Date[/TD]
[TD]Payment Date[/TD]
[TD]Due Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/09/2018[/TD]
[TD][/TD]
[TD]30/11/2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]29[/TD]
[TD]22[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18/06/2018[/TD]
[TD]18/07/2018[/TD]
[TD]16/09/2018[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01/08/2018[/TD]
[TD]13/09/2018[/TD]
[TD]30/10/2018[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01/08/2018[/TD]
[TD]25/10/2018[/TD]
[TD]30/10/2018[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]05/09/2018[/TD]
[TD]22/11/2018[/TD]
[TD]04/12/2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]31[/TD]
[TD]22[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/10/2018[/TD]
[TD]10/01/2019[/TD]
[TD]06/01/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]23[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18/10/2018[/TD]
[TD]25/02/2019[/TD]
[TD]16/01/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

Thanx in advance.

 

[footoo]

=max(min(d$2,if($b4="",today(),$b4))-max(eomonth(d$2,-1),$a4),0)

 

[Acekay]

Thanx a lot. Its perfectly serving my requirement.