Solved

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

Posted on 2016-09-29
6
42 Views
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.

Regards,
D Patel
Monthly-Rent.xlsx
0
Comment
Question by:DPatel
6 Comments
 
LVL 35

Expert Comment

by:PatHartman
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?
1
 
LVL 6

Author Comment

by:DPatel
ID: 41822070
Sorry,

The query is related to Excel.

I changed the Topic.
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41822098
Can you indicate what the result is that you are expecting
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 6

Assisted Solution

by:DPatel
DPatel 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.
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 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.
1
 
LVL 6

Author Closing Comment

by:DPatel
ID: 41829458
Thanks for the help...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

806 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