+ Reply to Thread
Results 1 to 13 of 13

Difference between Start And End Dates Then Distribute Through a 12-month Calendar

  1. #1
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Hi Everyone,

    I am trying to build something out formulaically which will find the difference between two dates (Start and End), and then distribute it out into a 12-month calendar.

    For example, with a Start date of 1/23/2019 and an End date of 4/1/2019, the difference is 68 days. I have 12 columns to the right each representing a month.

    Is there a way to build a formula that runs down 12 columns dispersing the count of 68 days throughout until the End date? So there would be 8 days in Jan-19, 28 days in Feb-19,31 days in March-19 and then 1 day in April-19.

    I have attached an example worksheet.

    I appreciate you taking a look at my question. Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    This should do it for you

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

  3. #3
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    This works! Thank you so much for you work. Would you mind explaining the formula? I would like to understand how it works. Many thanks.

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    I'll try .


    The least intuitive part is probably the part ROW(INDIRECT(D$2&":"&EOMONTH(D$2,0))) This sets up an array of numbers Fot the start of the month, in D2, and then end of the same month, by stringing the D2 date and the eomonth date into a string, D$2&":"&EOMONTH(D$2,0), passing that string to INDIRECT to get a range, and extracting the row numbers of that range. So for D2, eomonth D", we build an array 43466, 43467, 43468, ..., 43496 (remember Excel stores dates as the number of days since 1/1/1900). I then create a silar array of dates for the dates in A3 and B3, this gives an array of 34488, 43489, 43490, ..., 43556.

    To see if any of those dates in the array for the A3-B3 dates is within the array for the D2-EOMONTH(D2) dates, I do a simple MATCH. This returns another array of 22 #N/A, then 1,2,3,4,5,6,7,8,9 - this is because A3 starts on 23rd, so there are 22 no matches in January, and 9 matches.

    I then do an ISNUMBER on that array to turn the no matches to FALSE, the MATCHES to TRUE, athe the double unary -- turns them into 0s and 1. SUMPRODUCT adds them up to get the number of days between the two dates within that month.

    Finally I just check if the date in A3 is in the mothe being pro-rated for, geting a TRUE/FALSE result, and subtract that so that if the start date is in that month we ignore it (I would have said Jan had 9 days, but your example said 8, so I added this).

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

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Or try:

    =SUMPRODUCT(--(TEXT(D$2,"mmm-yyyy")=TEXT(ROW(INDIRECT($A3+1&":"&$B3)),"mmm-yyyy")))

  6. #6
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Seeing Phuocam's answer, mine can be simplified that trick of adding 1 to the start date

    =SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(D$2&":"&EOMONTH(D$2,0))),ROW(INDIRECT($A3+1&":"&$B3)),0))))

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,627

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Following Bob Phillips' earlier suggestion.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 08-23-2019 at 12:29 PM.
    Dave

  8. #8
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Hi Bob Phillips, your assumption of counting the start date if it's in the current month is right. So if I were to keep that criteria but disregard the End date -

  9. #9
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Just take my second formula without the +1

  10. #10
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Bob Phillips,

    I got it by subtracting -1 from the End Date. Date excel calculations is another concept I am learning it seems! Thank you again.

    =SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(D$2&":"&EOMONTH(D$2,0))),ROW(INDIRECT($A3&":"&$B3-1)),0))))

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  12. #12
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    Quote Originally Posted by wooshoe View Post
    I got it by subtracting -1 from the End Date. Date excel calculations is another concept I am learning it seems! Thank you again.

    =SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(D$2&":"&EOMONTH(D$2,0))),ROW(INDIRECT($A3&":"&$B3-1)),0))))
    Ah I see, you didn't want to include start and end date, but wanted to include the start date, so take 1 from the end date. It's a neat little addition to the toolbox isn't it

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,742

    Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar

    or can try
    D3=MAX(0,MIN($B3,EOMONTH(D$2,0))-MAX(EOMONTH(D$2,-1),$A3))
    Try this copy and paste across
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ 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. Find difference between two dates and build a calendar
    By wooshoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2019, 10:30 PM
  2. [SOLVED] Average Age Difference Between A Range of Start And End Dates
    By mgs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-10-2019, 11:56 PM
  3. Autopopulated Resource Calendar with Start and End dates
    By Pauly K in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-18-2017, 12:49 PM
  4. [SOLVED] Return a Month When Dates Cross over Calendar Month
    By Alewis2122 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-06-2016, 12:54 PM
  5. transfer start and end dates to a calendar
    By Jeff up North in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-12-2013, 06:39 PM
  6. Difference between Start and End dates
    By Dyzone in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2009, 08:43 AM
  7. Replies: 0
    Last Post: 03-27-2008, 04:36 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