How to use dates as reference in allocation of a certain amount based on different %

Originally Posted by Glenn Kennedy

If you look at your post, you will see that the forum has reformatted it, making it completely unintelligible.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi Glenn,

Thank you for taking the time to comment on my post. Appreciate it a lot. I have attached the file, hope you can help me on this one. Thanks again!

Originally Posted by JeteMc

Hello nickyme02 and Welcome to Excel Forum.
See if the following formula yields the expected results:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hi Jet,
Thanks for the response. However, can you twitch it a little? I've added some new columns and the expected result to better explain what I need here. Please see attached file.
If you can see from the columns, on 3 Apr 2017 , there's a $5,365.80 which is 1% of the total claim, it is reported under this column since the due date for the Pre-Construction claim is 28-March-2017. And the 3-Apr-2017 column represents all amounts received from a week's time, that is from 28-March to 3-April. Then this goes on so on and so forth on other claims.
So the formula should also be able to detect under which time period the claim falls due and reflect the amount that should be received within that time frame. I hope I explained it clearly. Thank you so much.

Wow! Thanks a lot! This actually works.

I just encountered one problem though, some due dates fall under the same date of the predefined columns and the index formula is picking up the next column date instead of the same column date.
Like for example I changed in red font color the slab claim due date to 8-May-2017. in the weekly column it should be reported under that same date 8-May-2017, however instead it is being reported under the 15-May-2017 column. I figured that it is because of the +1 in the index formula on columns BF12:BL15. But I figured as well that if we delete that then it would affect those whose due dates is a day after the cut off which shouldn't be the case.

I would greatly appreciate if you could tweak it a little that of which the formula would be able to detect the correct dates the claims should be reported.

Thank you very very very much!

Hi JeteMC, it worked well. Thank you so much for your time and effort!!! This is amazing