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

  • Thread starter Thread starter aflores1137
  • Start date Start date
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??
 
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
 
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?
 
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?
 
Back
Top