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

1. ## 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!  Register To Reply

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

This should do it for you

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## 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.  Register To Reply

4. ## 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).  Register To Reply

5. ## 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")))  Register To Reply

6. ## 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))))  Register To Reply

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

Following Bob Phillips' earlier suggestion.
Formula:  `Please Login or Register  to view this content.`  Register To Reply

8. ## 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 -  Register To Reply

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

Just take my second formula without the +1  Register To Reply

10. ## 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))))  Register To Reply

11. ## Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar  Register To Reply

12. ## Re: Difference between Start And End Dates Then Distribute Through a 12-month Calendar Originally Posted by wooshoe 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   Register To Reply

13. ## 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  Register To Reply

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