List today's date + 3 months until specified date?

Question

I'm trying to do an invoice date calculator in excel. What I have is the date when the first invoice is due and when the invoice runs out. I then have a field which holds the number of months we invoice.

What I need to be able to do is dynamically generate a list which shows which months invoicing is due. For example:

Invoice Start Date = 1st Jan 2012
Invoice End Date = 1st Jan 2013
Invoice every 3 months

The list would then output:

1st Jan 2012
1st Apr 2012
1st Jul 2012
1st Oct 2012

Obviously depending on the start month, end month or invoice every X days number, this list would expand and change accordingly.

Does anyone know how I can do this? I can add 3 months onto a date no problem, but getting a list to dynamically expand etc I can't do.

I'm not sure what your specific issue is. Are you trying to populate a Drop-down list with different values? Could you provide some code or a screenshot of your Worksheet so it is a little clearer what you are trying to do? — JustinJDavies
Basically, I can make the formulae in one cell and drag it down. Works great! But one invoice might run for a year, one might run for 2 years. Effectively, I would like the formulae to "automatically drag down" X number of rows until the last date is the end date of the invoice. Does that make any more sense? — K20GH
"automatically drag down" sounds very much like a task for VBA. Your formula might help to be sure or advice you further. — Jook
My formula is =DATE(YEAR(F11),MONTH(F11)+$F$13,DAY(F11)). F11 being the start date, F13 being the number of months to add. Basically if F11 contains Jan 1st 2012, then the cell with that formula will show 1st April 2012 — K20GH

Mischinab Mischinab · Accepted Answer · 2012-09-26T15:01:12

One option could be to have the formula return only dates which are below the end date, and blank in the other cases.

In your comments, you have a formula to calculate the next date based on the adjacent cell, which I assume goes into cell G11. The formula could first check to see if the new date will exceed the end date, and if so, return blank:

=IF(OR(DATE(YEAR(F11),MONTH(F11)+$F$13,DAY(F11))>$F$12 , F11=""),
 "",
 DATE(YEAR(F11),MONTH(F11)+$F$13,DAY(F11)))

In the first line, the formula checks to see if the value planned for G11 exceeds the maximum date, or the value on the left is blank. If this is the case, then blank will be returned, which signals the other cells to also return blank. If the date is valid, then the third line enter the date into the cell.

Of course, you don't have separate lines in excel formulas, so everything should be entered onto one line. The formula can then be copy & pasted into the adjacent cells, giving:

 F              G             H             I            J        K   L   M
------------+--------------+--------------+------------+------------+---+---+---+
Jan 1, '12  +  Apr 1, '12  +  Jul 1, '12  + Oct 1 '12  +  Jan 1 '13 +   +   +   +
------------+--------------+--------------+------------+------------+---+---+---+
Jan 1, '13  +              +              +            +            +   +   +   +
------------+--------------+--------------+------------+------------+---+---+---+
3           +              +              +            +            +   +   +   +
------------+--------------+--------------+------------+------------+---+---+---+

If you wanted this in the list, copy the formula for the maximum number of dates you envisage, and then the list will show the dates followed by blanks. There doesn't seem a way around the list including the blanks, but at least it will contain only dates which are valid.

List today's date + 3 months until specified date?

1 Answers