I would like to know the best formula for spreading an amount over a period when a date range is given.
Spreading Amount based on Date Range



What are your expected outcomes?

My expected outcome are as highlighted in Yellow on the attached excel sheet. I would like to know the amount per month based on the given date range

Sorry, this is not an expected outcome:
Excel 2016 (Windows) 32 bit [TABLE="class: head"]
[TR="bgcolor: #888888"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][SIZE=10px]11[/SIZE]
[/TD]
[TD="bgcolor: #FFFF00"][SIZE=10px]Amount for the month of March shall appear here[/SIZE]
[tr][td]
[/TD]
[/TR]
[/TABLE]
[TABLE="class: grid"]Sheet: Sheet1
[/td][/tr]
[/TABLE]
What I want to know is what you think the answer should be in that cell and why. 
Pls see attached. Expected answer the respective amount distribute over the duration on Monthly basis


Hi. Any one can help me on this?

You have posted this query on at least one other forum. Please provide links to anywhere else you have asked this, as per the forum rules.


Thank you.

Can you help me to solve this?


I don't fully understand your data. How is Excel meant to know that you are only interested in the top row of data? Where so the other rows with overlapping dates come in? This is why you are not getting any response  it's not clear what you are trying to achieve.

My expected answer should be the a respective amount with a start and end date to be distributed over each Month as per number of days on that particular Month.
I have attached fresh sheet with a single entry to be distribute over Months. However the formula I am looking for is to obtain output with many entries as similar.

But you have missed my point!
I understand what you want in the latest workbook. What I don't understand in the extended workbook from earlier is how you want Excel to deal with subsequest sets of start and end dates in the table at the top  where will they appear on the table at the bottom?
I am afraid that you haven't moved us any further forward: you are just repeating yourself without answering the specific questions asked.

I require a particular amount with a specif start and end date to be distributed over the Months based on the number of days in the particular month. at the bottom i am going to list the entire Months for a specific year and the amount shall be distributed over the months.

I'm sorry  I am not getting information from you in a way that is allowing me to fully understand what you want. I hope that someone else can assist.

Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!