Invoices issued (or planned to be issued) vs. collection (variable days per client)

Hi everyone,
I have done a search in the forum but did not find what I was after so I come with a plea for help. Also attaching an excel to explain my idea/ problem.

In short:

• Multiple clients. Each invoice can be at any point in time within that table.
• Each client has a different number of days after which collection is expected
• The value is copied from the Invoiced amount in the top part and appears in the relevant column in the bottom part. If the

I am assuming that this is possible with formulas even and most probably with VBA as well. Help please?
EXAMPLE SHEET.xlsx

PS: I am on Office365 for Mac.

Originally Posted by thenewkidd

i have a few questions as it appears to be a disconnect between time to collect vs Expected date.

what is the raw data and what do you want to be formula?
LT1511: raw data is literally a manual entry, in number format. The raw data will be the manual entry of the amount of invoice issued, in the column that represents the month in which it was issued. Then, the number of days which we expect are going to pass before we collect.

Is "Days to Collection" a raw number entered or is that a formula?
LT1511: a raw number, entered manually.

if it is a formula, how are you calculating "Days to Collection?" is it based on the date issued and basically a "countdown" clock at that point?
LT1511: it’s a good idea actually. The reality is though that this is a raw number that is based on experience with that specific client.

Why is Client1 45 Days to collection while Client4 is 90?
If all invoices issued were the same date, I need to understand why those would be two different numbers unless those are raw data and not calculated?
LT1511: correct, this is a raw number. We know from history that client 1 tends to pay in this amount of time, client 2 in that amount of time. It will be a manual entry. I actually wish it was not but it is what it is..

Answers within your text.

As per your current file structure, you need every time to change data & previous history you can't find in future.
If you are ready to re-structure data, then i have already maintain payment expected date & receipt date sheet, if yes then i will share with you, If you get fine, you go with those structure.

Ok. Here i am attaching file for your ready reference. Its very simple.