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

A

#### 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??

B

#### 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

A

#### 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?

B

#### 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?

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.