Cashflow forcast help (IF, Date Range Formula??)

[maz86]

Hi Guys - Hoping someone can help.

I'm trying to forecast my payments for the next year dependent on the frequency (Weekly, Monthly, Quarterly, Yearly) and based on the last payment date. (there is a lot data to do this manually)

1. I have column headings which are the week ending dates (Friday's), i.e Column E = 20/03/2015, Column F = 27/03/15 etc.
2. In Column A, I have list of dates a supplier was last paid, i.e A2 = 22/02/2015
3. In Column B, I have a list of amounts that have been paid, i.e B2 = £100.00
4. In Column C, I have the frequency of the payment (Weekly, Monthly, Quarterly, Yearly) - C2

What I'm trying to achieve is for a formula to return the value from cell B2 to the relevant cell if the last payment date equals the column heading or is within the last 7 days (Saturday to Friday), if not the cell to remain blank - I believe for this the year part of the date would be irrelevant. For example from the above data:

• If the supplier was paid weekly the £100.00 would be in cells E2 and F2
• If the suppler was paid monthly the £100.00 would only be in F2 as the date falls in that week of the month (22/02/2015 + 1 month = 22/03/2015).

My knowledge on excel isn't good enough and have tried different options to try and to get something to work with no luck and haven't come close. I'm assuming what I need will be a very long formula if all the frequency of the payments were to be included in one formula so would be happy for one that could be tweaked to take into account the different frequencies (this may help where I haven't mentioned frequencies which aren't common i.e every 2 or 6 months).

Any help or suggestions to the type of formulas that will be useful will be greatly appreciated.

Thank you in advance