0

From a list of contracts I want to learn which month the invoices are due. The invoice frequency is different per contract, ranging from monthly (12x) to quarterly (4x) and yearly (1x). Also the start dates are flexible.

Data input: contract data

Desired output: invoices per month

Approach

What I think I have to do is to create an intermediate data set which shows all invoices to be sent that year. Here's how that invoice data would look like: all invoices. After managing to create this invoice data I can then create a pivot table around the invoice month.

Question

My question is, first of all, is my approach the right one? And if so, how would I generate such an intermediate data source automatically?

Improve this question

1 Answer 1

Reset to default
0

You should be able to create the "invoices per month" table using simple EXCEL functions.

  • MONTH(date) ... returns the month number 1..12
  • MONTH(invoice month) - MONTH(contract date) ... Number of months since start date
  • (MOD(the above, 12/frequency) = 0) ... True if its an invoice month

Now the only caveat is that the above will fail as soon as your cross calendar years, so to correctly count months since the start date including also a first invoice month:

  • MONTH(invoice month) + 12*(YEAR(invoice date)-YEAR(contract date)) - (MONTH(contract date) + first invoice month #)
Improve this answer
1
  • 1
    Thanks for showing me this much simpler solution. Turns out I was overcomplicating it.
    – J Abert
    Commented Feb 22, 2022 at 12:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .