Continuity - Selecting Value based on range of dates (that change)

[yhernandez]

Here is the use case.

A have column headings that are dated friday of each week. Each friday, I updated the first column heading, and the rest ones update themselves into the forecast.

I'm doing a cashflow forecast. Based on the week/month, I apply certain costs to that column. I pull data from other tabs. Here is what I want to add.

Let's say we have a recurring expense $25,000 that occurs on the 15th and 31st every month. I want to do an If statement or Lookup that populates the cell if that column is the closest column to the 15th of the month (but not over), and the end of the month (but not over). As you update the sheet each week and advance the date, the historical numbers fall off. I've included two screenshots to simulate what I am trying to do.

Thanks,

Any pointers/ideas suggestions?

 

[herofox]

to better if you send the excel file

 

[yhernandez]

Sure. It is attached. The date calculation needs to be relative so that as we update the interval it can move w/it. In sample spreadsheet, the dollar amounts are hard coded. Thats where the formulas should ideally go.

 

[herofox]

This is difficult to make this as you want

 

[yhernandez]

It is indeed. Im trying this route:

=IF(MATCH((EOMONTH(G3,-1)+15),G3),$E$25,IF(MATCH((EOMONTH(G3,0)),G3),$E$25,"NA"))

But not really working out.

 

[yhernandez]

One down. I can populate end of month. Via this formula. Now need to crack middle of month.

=IF(MONTH(G3)=MONTH(H3),0,$E$26)