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.

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.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

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.