Generate list of invoice dates based on start date and invoice frequency

Hi,

I have been driving myself crazy with this problem. I am trying to build a formula that automatically generates a list of invoice dates based on 3 dynamic inputs: start date, a full years revenue amount and invoice frequency. The inputs looks like this:

Start date: 01/01/2000
Year 1 full revenue: 850,000 (this is adjustable)
Invoice frequency: 3 (this has to be adjustable, and will often be larger than 12)

Based on these inputs I want to automatically generate a list of invoice dates. So with the above values, it would give me the following list:

01/03/2000
01/06/2000
01/09/2000
01/12/2000

But let's say I then change the invoice frequency to 24 months, the output would be only:

01/12/2001


So far I have built out the solution in two steps.

Step 1: =MOD(1,$E$3)=0

Checks whether the invoice frequency in months divides evenly into the number of months that have passed since the start date. If the answer is 0, it's time for an invoice.

Step 2: =IF(G3=TRUE, EDATE($E$1, 1), "")

This checks if the output of MOD is TRUE. If the output is true, it adds the number of months that have passed to the original start date and displays the date.

The problem with this solution is that I need to run the formula for every POTENTIAL number of months that have passed in the future, and I want to display the invoice dates for the next 25 years. So I have to have a long list of numbered rows. Below is a screenshot for clarification:

problem.png

What I want, is to just paste a formula in one cell and it automatically 'spits out' all future invoice dates without me needing to list a range of eventual dates. I assume I need some sort of OFFSET formula, but I can't figure out the next steps! Any help would be much appreciated. Thank you in advance!

I attached my workbook but don't pay attention to the other columns, these are problems for later

Not sure that I understood your requirement... but try something like...

If you have access to Office 365...
=DATE(YEAR($E$1),SEQUENCE($E$3,,ROUNDUP(12/$E$3,0),3),DAY($E$1))

If not...
=IF(ROWS($A$1:A1)<=$E$3,DATE(YEAR($E$1),MONTH($E$1)+3*ROWS($A$1:A1),DAY($E$1)),"")

Copy down.

Hi, thank you so much for the quick reply. I think I wasn't clear about the problem. If the invoice frequency is 6 months, an invoice would come once every 6 months, not 6 times. Hope that makes sense? So if your invoice frequency is 7, it means one invoice is sent every 7 months. If the invoice frequency is 24 months, it means the first invoice is sent after 2 years.

Also I changed something in my original post; if the invoice frequency is 3 months, it should NOT stop counting at the end of the year. If the start year is 2000, it should give the invoice date every 3 months into the next 25 years.

So, is it always going to be 25 years?

It would be easier if you can provide more sample (i.e. desired output based on input).

Try something like below... Assuming 25 year period.

Formula:

Please Login or Register  to view this content.

Copy down as much as required.

You may need to adjust based on "inclusive" vs "exclusive" type calculation as needed.