+ Reply to Thread
Results 1 to 7 of 7

Formula to fill month end dates from start date to end date

  1. 04-03-2016, 10:28 AM #1
    Barieq
    Barieq is offline
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    521

    Formula to fill month end dates from start date to end date

    Hi,

    Can anyone help me fix the formula to fill month end dates from start date until the end date.


    Start Date 01/14/16
    End Date 07/28/16


    01/31/16 02/29/16 03/31/16 04/30/16 05/31/16 06/30/16 07/28/16

    Last date should be same as End Date.

    Thanks & regards,
    Barieq
    Reply With QuoteRegister To Reply
  2. 04-03-2016, 10:35 AM #2
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Formula to fill month end dates from start date to end date

    Hi
    try to use
    =EOMONTH(Start Date,0)
    to next month use =EDATE(Start Date,ROWS($A1:A1)) and copy down
    Last edited by José Augusto; 04-03-2016 at 10:37 AM.
    Reply With QuoteRegister To Reply
  3. 04-03-2016, 10:40 AM #3
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to fill month end dates from start date to end date

    With these values in A1:B2
    Please Login or Register  to view this content.

    This formula begins the date list:
    Please Login or Register  to view this content.
    And this formula, copied down as far a you think you'll need, continues the list, calculating month end dates until the End Date month is reached. At that point, it returns the End Date. After that, the formulas returns text blanks
    Please Login or Register  to view this content.
    If you need the dates to list to the right, use this formula:
    Please Login or Register  to view this content.
    In that example, these values are returned in D1:D7
    Please Login or Register  to view this content.
    or...D1:J1
    Please Login or Register  to view this content.
    Is that something you can work with?
    Last edited by Ron Coderre; 04-03-2016 at 10:43 AM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  4. 04-03-2016, 10:44 AM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to fill month end dates from start date to end date

    or maybe this

    Formula in C1 and copy across

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    v A B C D E F G H I
    1 1/14/2016 7/28/2016 1/31/2016 2/29/2016 3/31/2016 4/30/2016 5/31/2016 6/30/2016 7/28/2016
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 04-03-2016, 10:58 AM #5
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Formula to fill month end dates from start date to end date

    Assuming Start date in A1, and End date in A2.

    Formula in A4:

    =IF(COUNTIF(A$3:A3,A$2),"",MIN(EOMONTH(A$1,ROWS(A$4:A4)-1),A$2))

    copy down
    Reply With QuoteRegister To Reply
  6. 04-03-2016, 11:31 AM #6
    Barieq
    Barieq is offline
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    521

    Re: Formula to fill month end dates from start date to end date

    Thanks for All
    Reply With QuoteRegister To Reply
  7. 04-03-2016, 11:51 AM #7
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Formula to fill month end dates from start date to end date

    You're Welcome!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Generate Dates by Month along columns based on Start Date and End Date
    By Stndsh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2015, 05:32 AM
  2. [SOLVED] Help in writing formula in excel to produce dates based on start date and end date
    By shoot for moon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 01:00 PM
  3. Fill table with dates given start and end date
    By harryhermit in forum Excel General
    Replies: 3
    Last Post: 12-29-2013, 10:21 AM
  4. [SOLVED] Formula For Sum of Spend Per Month During a Start and End Date
    By t_man000 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-12-2013, 09:13 AM
  5. Using VBA to fill col with dates based on start and end date
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-22-2011, 11:58 AM
  6. Automatically fill in dates two weeks and one month ahead of date inputted manually
    By LKC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2011, 09:10 AM
  7. Looping Generating Specific Dates in Six Month Intervals between end and start date
    By LenaJ887 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-16-2010, 02:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1