Using OFFSET between two dynamic dates

[bananafay]

Hi there,

In my daily workbook I have an invoice date that changes every month and the payment date is always on the 2nd of the following month.

I'm trying to create a formula to flag the payment date and show the corresponding cash outflow on the payment date.

Using the OFFSET function, the reference is the invoice date however the column number is dynamic and changes depending on whether it is the 2nd of the month. I've tried using OFFSET and MATCH together but can't work out how to calculate the number of days between the invoice date and the 2nd of the following month.

If anyone could point me in the right direction for the type of formula to use, it would be greatly appreciated.

 

[Dermot]

This isn't clear to me. A simple example would help.

 

[bananafay]

This isn't clear to me. A simple example would help.

Hi Dermot

Apologies, I have found a solution. What I was trying to do was use a complicated OFFSET formula with the wrong reference, when simply summing the invoice payments for each month on the second of the of the following month is a much more elegant solution. Thanks for your reply, I will mark this as solved and save everyone from my brain fog moment!

Thanks
Deana

 

[Fluff]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.