Splitting Date Range into Months

[AH2014]

Hope someone can help. I am using excel 2010. I have over 100 rows that need to be split by month for billing purposes.

I have a begin date (a1) and end date (a2); other cells may contain name and address, amounts.
A1 01/01/14 A2 7/15/14

Is there a way to get a new row for each month?
01/01/14 01/31/14
02/01/14 02/28/14
etc.
end with 07/01/14 07/15/14

Thanks for any help.

 

[AH2014]

I found a old post from 2008. Someone split by years and I tried it and worked. Can anyone figure out how to convert the formula to MONTHS vs YEAR???? I tried can't get it to work.

This is what posted for years.....

A2 = A1
B2 = IF(YEAR(A2)=YEAR($B$1),$B$1,DATE(YEAR(A2),12,31))
A3 = IF(A2="","",IF(YEAR(A2)>=YEAR($B$1),"",DATE(YEAR(A2)+1,1,1)))
B3=IF(A3="",",IF(YEAR(A3)=YEAR($B$1),$B$1,DATE(YEAR(A3),12,31)))

Fill the formula for A3 and B3 down as far as you need.

 

[Deepak Churiwal]

Try writing the below formula in cell A3 and then drag it down


=TEXT(IF(EOMONTH($A$1,COUNTA(A$1:A1)-2)+1<$A$2,EOMONTH($A$1,COUNTA(A$1:A1)-2)+1,""),"mm/dd/yy")&" - "&TEXT(IF(EOMONTH($A$1,COUNTA(A$1:A1)-1)<$A$2,EOMONTH($A$1,COUNTA(A$1:A1)-1),$A$2),"mm/dd/yy")

 

[ukmikeb]

Hi

Perhaps :-

Excel Workbook
	A	B
1	01/01/2014	15/07/2014
2	01/01/2014	31/01/2014
3	01/02/2014	28/02/2014
AH2014

Excel 2007

Cell Formulas
Range	Formula
A2	=A1
A3	=IF(EOMONTH(A2,0)+1>$B$1,"",EOMONTH($A2,0)+1)
B2	=IFERROR(IF(EOMONTH($A2,0)>$B$1,$B$1,EOMONTH($A2,0)),"")
B3	=IFERROR(IF(EOMONTH($A3,0)>$B$1,$B$1,EOMONTH($A3,0)),"")

A3 and B3 dragged down as far as required.

hth

 

[AH2014]

Mike,

Thank you!!!! It worked. I appreciate all your help.

 

[AH2014]

Deepak,

It did not work. Thanks for trying.