Allocate rent

[Thomas Kuriakose]

Respected Sirs,

We would like to allocate the rent values monthly for each name based on the start date end date of the rent.

There is a condition - if the start date and end date is greater than 15th of the month, the values should go to the next month.

Kindy find attached the file for your reference.

kindly guide on how to allocate the rent month wise. The first value has allocated based on the months.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

Maybe,

1] All date in G1:AA1 changed from 1st to 15th of the month.

2] G2, copied across and down :

=IF(AND($C2<G$1,$D2>G$1),$B2/ROUND($E2,0),"")

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you so much for the solution provided.

Sir, kindly suggest the correct formula for the months in E2 down, as we have used (d2-c2)/30, some dates cross over to the next month due to the decimal values - 2.134, 2.56. This can cause the allocation be further distributed and carry over to partial values.

Thank you very much once again.

with regards,
thomas