Date formula to bring back specific dates after an initial date

[Ayla_uk]

Hi there,

I am trying to put together a formula to bring back up to 6 specific dates which are specific months after an initial date.
E.g. 6 payments that are 6 months apart.

Here is an example with the formulas I've used (which are probably very long winded as I'm new to this!!) and this example is based on 6 payments that are 6 months apart.

NOTE: (N8) is the cell with the initial date in it
(AD4) is the number of months between payments as this may vary from 6.
(AF4) is the Payment 1 date of 30 April 2016

Initial date:
31 October 2015
Payment 1 - 30 April 2016 - FORMULA =DATE(YEAR(N8),MONTH(N8)+ad4,DAY(DATE(YEAR(N8),MONTH(N8)+ad4+1,0)))
Payment 2 - 31 October 2016 - FORMULA =DATE(YEAR(AF4),MONTH(AF4)+AD4,DAY(DATE(YEAR(AF4),MONTH(AF4)+AD4+1,0)))
Payment 3 - 30 April 2017 - FORMULA for the remaining payments is similar to the formula for Payment 2
Payment 4 - 31 October 2017
Payment 5 - 30 April 2018
Payment 6 - 31 October 2018

The above works nicely if the initial date is at the end of the month. However, if the initial date is not the last day of the month, say 15 October 2015 then I would like the formula to bring back 15 April, 15 October each time and this is what I'm stuck on because I need the formula to do both. E.g. if the day is not the last day of the month then bring back this, otherwise if it is then bring back this.

Hopefully that's reasonably clear and if someone is willing to help I'd be grateful!!

Thanks.

 

[Dave Patton]

Excel 2010
	N	O	P
8	31-Oct-15	15-Oct-16	15-Oct-15
9	30-Apr-16	15-Apr-17	15-Apr-16
10	31-Oct-16	15-Oct-17	15-Oct-16
11	30-Apr-17	15-Apr-18	15-Apr-17
12	31-Oct-17	15-Oct-18	15-Oct-17
1c

Cell Formulas
Range	Formula
N9	=EOMONTH(N8,6)
N10	=EOMONTH(N9,6)
N11	=EOMONTH(N10,6)
N12	=EOMONTH(N11,6)
O9	=EDATE(O8,6)
O10	=EDATE(O9,6)
O11	=EDATE(O10,6)
O12	=EDATE(O11,6)
P9	=IF(DAY(P8+1)=1,EOMONTH(P8,6),EDATE(P8,6))
P10	=IF(DAY(P9+1)=1,EOMONTH(P9,6),EDATE(P9,6))
P11	=IF(DAY(P10+1)=1,EOMONTH(P10,6),EDATE(P10,6))
P12	=IF(DAY(P11+1)=1,EOMONTH(P11,6),EDATE(P11,6))

 

[Ayla_uk]

Hi Dave,

Column P does the trick. Thank you so much that is awesome!!! So much simpler than the route I was going down

Thank you!

 

[Dave Patton]

Thanks for the feedback. Dave