Date formula to bring back specific dates after an initial date

Ayla_uk

New Member
Joined
Sep 27, 2016
Messages
2
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">31-Oct-15</td><td style="text-align: right;;">15-Oct-16</td><td style="text-align: right;;">15-Oct-15</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">30-Apr-16</td><td style="text-align: right;;">15-Apr-17</td><td style="text-align: right;;">15-Apr-16</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">31-Oct-16</td><td style="text-align: right;;">15-Oct-17</td><td style="text-align: right;;">15-Oct-16</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">30-Apr-17</td><td style="text-align: right;;">15-Apr-18</td><td style="text-align: right;;">15-Apr-17</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">31-Oct-17</td><td style="text-align: right;;">15-Oct-18</td><td style="text-align: right;;">15-Oct-17</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">1c</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N9</th><td style="text-align:left">=EOMONTH(<font color="Blue">N8,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O9</th><td style="text-align:left">=EDATE(<font color="Blue">O8,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P9</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">P8+1</font>)=1,EOMONTH(<font color="Red">P8,6</font>),EDATE(<font color="Red">P8,6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N10</th><td style="text-align:left">=EOMONTH(<font color="Blue">N9,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O10</th><td style="text-align:left">=EDATE(<font color="Blue">O9,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P10</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">P9+1</font>)=1,EOMONTH(<font color="Red">P9,6</font>),EDATE(<font color="Red">P9,6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N11</th><td style="text-align:left">=EOMONTH(<font color="Blue">N10,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O11</th><td style="text-align:left">=EDATE(<font color="Blue">O10,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P11</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">P10+1</font>)=1,EOMONTH(<font color="Red">P10,6</font>),EDATE(<font color="Red">P10,6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N12</th><td style="text-align:left">=EOMONTH(<font color="Blue">N11,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O12</th><td style="text-align:left">=EDATE(<font color="Blue">O11,6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P12</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">P11+1</font>)=1,EOMONTH(<font color="Red">P11,6</font>),EDATE(<font color="Red">P11,6</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Ayla_uk

New Member
Joined
Sep 27, 2016
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,894
Messages
5,598,724
Members
414,253
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top