+ Reply to Thread
Results 1 to 8 of 8

Showing the months between 2 date fields

  1. #1
    Registered User
    Join Date
    06-21-2016
    Location
    Liverpool, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    25

    Post Showing the months between 2 date fields

    Hey Guys,

    Not sure if this is possible but is there a way to show all of the months between 2 date fields?

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Showing the months between 2 date fields

    Assuming you have dates in A1 & B1

    tRY

    =MONTH(b2)-MONTH(a1)
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    06-21-2016
    Location
    Liverpool, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    25

    Re: Showing the months between 2 date fields

    I can get the value easy enough what i am trying to do is display all the months for example.

    Start Date = 01/10/2016
    End Date = 01/02/2017

    Result would be

    01/10/2016
    01/11/2016
    01/12/2016
    01/01/2017
    01/02/2017

    Now i know i cannot put this into a single cell but trying to think of possible ideas outside the box.

    Basically the start date will have a currency value assoicated with it which i am trying to show the value over the period of time between the 2 dates in form of a chart.

    Is that possible or am i going about it all the wrong way?

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Showing the months between 2 date fields

    Start Date in A1.

    End Date in A2.

    Try this formulas in A3:

    =IF(EDATE($A$1,ROWS($A$3:A3)-1)>$A$2,"",EDATE($A$1,ROWS($A$3:A3)-1))

    Copy down.

  5. #5
    Registered User
    Join Date
    06-21-2016
    Location
    Liverpool, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    25

    Re: Showing the months between 2 date fields

    H'mmm that works was not expecting that.

    The next issue i have is using this method try to apply it to how i want it to work.

    Any suggestions?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Showing the months between 2 date fields

    How do you want it to work?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    06-21-2016
    Location
    Liverpool, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    25

    Re: Showing the months between 2 date fields

    Hi Ali,

    I think I may have drifted slightly off topic as the original request has actually been dealt with but doesn't actually deal with the current issue I am experiencing.

    I have a sheet of data currently with a start date an end date with a monthly currency value. I am trying to see if there was a way of excel identifying the months between the two dates which Phuocam has answered on how to do that with the above post.

    The issue is how do I enforce this when I am dealing with multiple rows of data each with all different start and end dates in a worksheet.

    The end result is for me to display this in terms of a piovt chart but I am not too sure if I can manipulate the data set in this way.

    The more and more I think about it the more am coming to grips that it cannot be done that way.

    I am currently working on an alternative solution to my issue which is just to display the next 6 months inside a table but haven't quite perfected the formula to do that yet.
    Last edited by Excel-Guru-1987; 10-24-2016 at 10:32 AM. Reason: Grammar Correction

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Showing the months between 2 date fields

    Maybe if you attach a sample workbook?

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Graph not showing months ?
    By dougers1 in forum Excel General
    Replies: 1
    Last Post: 11-01-2015, 07:06 AM
  2. [SOLVED] Referencing months and dates of date fields in a formula
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2014, 08:33 PM
  3. [SOLVED] Add date + 3 months in vba so the VBA sends mail reminder exactly 3 months before due date
    By JimmyQ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2014, 08:54 AM
  4. Charts: Only showing months with data
    By amartino44 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-05-2013, 03:53 PM
  5. Date-line and chart not showing blank cells
    By D-smoke in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-18-2012, 11:52 AM
  6. Excel 2007 : Showing negative months
    By hpasso217 in forum Excel General
    Replies: 1
    Last Post: 03-04-2011, 06:20 PM
  7. [SOLVED] Use a calc to figure sum in months using date fields but numeric r
    By susiespassion in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-06-2005, 08:08 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1