Payment term and cash flow (formula linked with costs) - weekly cash flow

[GSP21]

Hi,

There was a thread 'Payment term and Cash flow (formula linked with costs)' in January 2021 where someone was able to come up with a formula that automatically calculated the value and input it in to the correct place in the cash flow based on the payment terms. I tried the formula from that thread (see below) which worked perfectly but my cash flow is weekly not monthly and I do not know how to amend the below formula for a weekly cash flow (or even if that is possible).

Also the data that I receive is given to me monthly because the 'measure date' is monthly but a weekly cashflow is required - is there a way to convert the monthly data to be weekly - e.g. calculating daily amount then multiplied by days in week?

Thank you for any help that you can provide.

=IFERROR(IF(SUM($F5:F5)=0,0,IF(AND(INDEX($F$4:$Q$4,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0))+$S19>F$18,INDEX($F$4:$Q$4,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0))+$S19<EOMONTH(F$18,0)),INDEX($F5:$Q5,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0)),IF(INDEX($F$4:$Q$4,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0))+$S19>F$18,0,INDEX($F5:$Q5,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0)+COLUMNS($F19:F19)+1-IFERROR(MATCH(TRUE,INDEX($E19:E19>0,0),0),1))))),"")

​

 

[jorismoerings]

Hi and welcome to MrExcel,

Don't know if you still awaiting an answer.
If that's the case please share with us some sample data and show us manually calculated what the expected outcome would be.
Please use XL2BB to do so, preventing us to do a complete type over.
The link to Xl2BB is to be found here

Providing us with more info is usually the better way because it helps us to help you.