# Days in each month for a time span

1. ## Days in each month for a time span

I am looking for a formula that will tell me how many days in each month for which a given time span is active. For example the time span 1/1/2014-2/28/14. I want to know the time span is active for 31 days in January and 28 days in February. Another example: time span of 1/15/2014-2/28/2014. I want to know that the time span would be active for 16 days in January and 28 days in February. I am looking for a formula that will capture the amount of days in each month for which the time span covers. Please help, as I only know how to write a formula with multiple if statements that require a different formula for each given month, rather than one formula that applies to all months. Thank you in advance for your suggestions and assistance.  Register To Reply

2. ## Re: Days in each month for a time span

If you're counting the start date then for 1/15/2014-2/28/2014 there would be 17 days for January.

Try this...

Data Range
 A B C D E 1 Start End Month/Year Days 2 1/15/2014 2/28/2014 January 2014 17 3 February 2014 28 4 ------ ------ ------ ------ ------

Enter this formula in D2:

=IF(DATE(YEAR(A\$2),MONTH(A\$2)+ROWS(D\$2:D2)-1,1)<B\$2,TEXT(DATE(YEAR(A\$2),MONTH(A\$2)+ROWS(D\$2:D2)-1,1),"mmmm yyyy"),"")

Enter this formula in E2:

=IF(MAX(A\$2,DATE(YEAR(A\$2),MONTH(A\$2)+ROWS(E\$2:E2)-1,1))<B\$2,MIN(DATE(YEAR(A\$2),MONTH(A\$2)+ROWS(D\$1:D2)-1,0),B\$2)-MAX(A\$2,DATE(YEAR(A\$2),MONTH(A\$2)+ROWS(D\$2:D2)-1,1))+1,"")

Select D2:E2 and copy down until you get blanks.  Register To Reply

3. ## Re: Days in each month for a time span

Thank you Tony for your quick response. The solution works perfectly; however, is there anyway to get the month and year in d2 and d3 to go horizontal in d1 and e1 and have the days under that in d2 and e2 respectively?  Register To Reply

4. ## Re: Days in each month for a time span

Like this...

Data Range
 A B C D E 1 Start End ------ January 2014 February 2014 2 1/15/2014 2/28/2014 17 28

Enter this formula in D1:

=IF(DATE(YEAR(\$A2),MONTH(\$A2)+COLUMNS(\$D1:D1)-1,1)<\$B2,TEXT(DATE(YEAR(\$A2),MONTH(\$A2)+COLUMNS(\$D1:D1)-1,1),"mmmm yyyy"),"")

Enter this formula in D2:

=IF(MAX(\$A2,DATE(YEAR(\$A2),MONTH(\$A2)+COLUMNS(\$D2:D2)-1,1))<\$B2,MIN(DATE(YEAR(\$A2),MONTH(\$A2)+COLUMNS(\$D2:D2)+1-1,0),\$B2)-MAX(\$A2,DATE(YEAR(\$A2),MONTH(\$A2)+COLUMNS(\$D2:D2)-1,1))+1,"")

Select D1:D2 and copy across until you get blanks.  Register To Reply

5. ## Re: Days in each month for a time span

Thats it, perfect. Thanks Tony, I've only posted twice and you helped me out both times, your a lifesaver. I sincerely appreciate the help.  Register To Reply

6. ## Re: Days in each month for a time span

You're welcome. Thanks for the feedback! In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.  Register To Reply

7. ## Re: Days in each month for a time span

Another variation...........

Assuming A2 has start date and B2 has end date, then in C2 place the formula given below and drag horizontally until you get blank cells. ``Please Login or Register  to view this content.``  Register To Reply

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