Excel - Autofill Monthly Rent according to Start Date, End Date and Payment with year change

Posted on 2016-09-29
Medium Priority
Last Modified: 2016-10-05
Hi Experts,

I have prepared one sheet related to monthly rental. Formula which I entered is working as well.

Is there any shortest way to fill the months column ("C" to "N") with Payment Column ("O") according to Start ("A") and End Dates ("B"). The dates could be in combinations of last year and current year, current year and next year, or current year.

I have attached the sample sheet herewith for your kind reference.

D Patel
Question by:D Patel
LVL 41

Expert Comment

ID: 41821993
Excel is good for a lot of things but databases are better for managing data.  I see that you have Access included as a topic.  Does that mean that you are interested in Access as a solution?

Author Comment

by:D Patel
ID: 41822070

The query is related to Excel.

I changed the Topic.
LVL 23

Expert Comment

by:Roy Cox
ID: 41822098
Can you indicate what the result is that you are expecting
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!


Assisted Solution

by:D Patel
D Patel earned 0 total points
ID: 41822184
The same result as depicted in the sheet.

But with different alternative formula. Because my formula is very lengthy.
LVL 25

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 41822550
Remove Numbervalue from the formula, and the formula will be much shorter.
Year and Month fomulas returns numbers, so it is not needed.

Author Closing Comment

by:D Patel
ID: 41829458
Thanks for the help...

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question