Hi,
I'm trying to make a direct debit forecasting tool that auto plots the payment amount at the relevant date for the relevant amount given the information on the direct debits from a table. The formula I have so far is:
The first part of the formula is checking that the date in question is equal to the payment date from the table, the second part is making sure that the date in question is less than the payment end date. Its then looking up the value if the conditions are met.
What I need is the ability to make sure that any payments are posted for the month regardless of the length of the month. I.e. My columns are the days of the week for each day of each month all the way to 2023. But if a payment date is 31st and there are only 30 days in the month, it wont post the payment into the cell.
How can I add this functionality to my formula? I have a helper row (5:5) that puts the last day of the month in the column on the last day (i.e. it will put 28 on feb 28 so that is identifying when the last day of the month is, i just need to know how to compare the payment date to that value in the formula I already have.
Thanks in advance for any help.
Bookmarks