Calculate Amount between two dates

[arpanpanchal]

Hi friends

I have attached the example worksheet in which I want to calculate the amount between two dates but it should calculate based on months. I have done manual calculation in the attached example.

Basically, the rent between 1/7/2019 to 28/3/2020 should be calculated as 8 months (Jul - Feb) x 3000 months = 24000, then monthly amount should be divided by the number of days in broken month (which is March - so it will be 31 days) and then the daily rent should be multiplied by number of days applicable as per date (which is 28 days - because of 28/3/2020), so the March Rental should be calculated as $3000/31 days x 28 days = $2,709.68. So the total rent from 1/7/2019 - 28/3/2020 is $24000 plus $2,709.68 = $26,709.68.

I have tried a lot but cant work out. Really appreciate if anyone can assist with this.

Thanks a lot in advance.

[bebo021999]

Try in E2:

Please Login or Register  to view this content.

[arpanpanchal]

Thanks a lot friend. That works perfect and expected. What should be the change if the amount in B2 is annual instead of month, i.e $36,000.

[JeteMc]

It would seem to me that the change could be as highlighted in orange:

Please Login or Register  to view this content.

Let us know if you have any questions.

[arpanpanchal]

Perfect, thanks a lot

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.