Fomula for number of days on each month from a date range

[aflores1137]

Hello All I need help

Basically I need to have the formula to reflect the number of days each
month till the end of a date

and whenever I enter in a date range each month updates itself with the
number of days

For example

Start Date 9/6/06
End Date 2/6/07


Year 2006- Year 2007-
Jan: Jan:31
Feb: Feb: 6
Mar:
April:
May:
June:
July:
August:
September:25 (including start date day)
October: 31
November: 30
December: 31

Totaling 154 Days


Im Thinking A2 is start date B2 is End Date
Then D2 E2 F2 G2 etc shows each number of days..
Can Someone Help??

 

[Biff]

Here's one way. The output will be different!

A2 = start date = 9/6/2006
B2 = end date = 2/6/2007

D1 = header = Month/Year
E1 = header = Days

Enter this formula in D2:

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

Enter this formula in E2:

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

Select both D2 and E2 and copy down until you get blanks. The output will
look like this:

...................D.....................E
1.......Month/Year...........Days
2......September 2006......25
3......October 2006..........31
4......November 2006.......30
5......December 2006........31
6......January 2007............31
7......February 2007...........6

Biff

 

[aflores1137]

I tried pasting the formula on D2 and it states that as of Date "Month"
for


MONTH­(A$2)+ROWS($1:1)-1 IS Invalid?? is there somthing im doing
wrong?

 

[Biff]

Here's a sample file based on your posted data:

Months-Days.xls 17.0 kb

http://cjoint.com/?ljegzH0xqU

Biff

I tried pasting the formula on D2 and it states that as of Date "Month"
for


MONTH­(A$2)+ROWS($1:1)-1 IS Invalid?? is there somthing im doing
wrong?