Generating list of dates based on start and end date

[lxiscs]

What I want to do is be able to enter a start and end date in two cells on one sheet and have all the dates from the start date to the end date be entered in a row on the next sheet.

This is what I have so far, with the dates done using the fill method from a start date. The values in the rows below are assigned based on conditional statements that refer to the dates above the cell. I want to start at cell B2 with the start date then fill in all the dates until it reaches the end date. In the column after the end date I have simple summing formulas for each row that I want to be automatically adjusted so they are always at the end of the dates and always sum the whole row.

<table border="0" cellpadding="0" cellspacing="0" width="792"><colgroup><col style="mso-width-source:userset;mso-width-alt:2633; width:54pt" span="11" width="72"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:54pt" height="20" width="72">01-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">02-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">03-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">04-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">05-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">06-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">07-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">08-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">09-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">10-Sep-11</td> <td class="xl68" style="border-left:none;width:54pt" width="72">11-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none" height="20"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 145.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none" height="20"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:black;mso-pattern:black none"> $ - </td> <td class="xl67" style="border-top:none;border-left:none"> $ 111.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 111.00 </td> <td class="xl67" style="border-top:none;border-left:none"> $ 111.00 </td> </tr> </tbody></table>
Any help would be greatly appreciated. Thanks.

 

[markmzz]

If I understand correctly what you want, so I think this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Start</td><td style="text-align: right;;">7/11/2011</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">End</td><td style="text-align: right;;">7/21/2011</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">7/11/2011</td><td style="text-align: right;;">7/12/2011</td><td style="text-align: right;;">7/13/2011</td><td style="text-align: right;;">7/14/2011</td><td style="text-align: right;;">7/15/2011</td><td style="text-align: right;;">7/16/2011</td><td style="text-align: right;;">7/17/2011</td><td style="text-align: right;;">7/18/2011</td><td style="text-align: right;;">7/19/2011</td><td style="text-align: right;;">7/20/2011</td><td style="text-align: right;;">7/21/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 145.00 </td><td style="text-align: right;;"> $ 145.00 </td><td style="text-align: right;;"> $ 145.00 </td><td style="text-align: right;;"> $ 145.00 </td><td style="text-align: right;;"> $ 145.00 </td><td style="text-align: right;;"> $ 145.00 </td><td style="text-align: right;;"> $ 145.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 111.00 </td><td style="text-align: right;;"> $ 111.00 </td><td style="text-align: right;;"> $ 111.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=Sheet1!B1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">B2<Sheet1!$B2,Sheet2!B2+1,""</font>)</td></tr></tbody></table></td></tr></table><br />

Markmzz

 

[lxiscs]

That will work for filling in the dates. Is there a way to have a column labeled Totals that would sum each row and be shifted to the end of the row of dates automatically? Also, the formulas in the rows beneath the dates would need to be copied or overwritten so they are below every date.

 

[markmzz]

That will work for filling in the dates. Is there a way to have a column labeled Totals that would sum each row and be shifted to the end of the row of dates automatically? Also, the formulas in the rows beneath the dates would need to be copied or overwritten so they are below every date.

Could you post a example of your data with the formulas?

Use the link below for helper to post.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz

 

[lxiscs]

Something like the following:

Excel Workbook
	AM	AN	AO	AP
22	10-Sep-11	11-Sep-11	12-Sep-11	Totals
23	$145.00	$145.00	$159.50	$ 449.50
24	$111.00	$111.00	$111.00	$ 333.00
TEST AREA

Excel 2007

Cell Formulas
Range	Formula
AM22	=IF(AL2<'Travel Worksheet'!$F$19,Lodging!AL2+1,"")
AM23	=IF(AND('Travel Worksheet'!$F3<=AM$22,AM$22<='Travel Worksheet'!$G3),IF(AM$22>='Travel Worksheet'!$M3,'Travel Worksheet'!$N3,'Travel Worksheet'!$L3),0)
AM24	=IF(AND('Travel Worksheet'!$F4<=AM$22,AM$22<='Travel Worksheet'!$G4),IF(AM$22>='Travel Worksheet'!$M4,'Travel Worksheet'!$N4,'Travel Worksheet'!$L4),0)
AN22	=IF(AM22<'Travel Worksheet'!$F$19,Lodging!AM2+1,"")
AN23	=IF(AND('Travel Worksheet'!$F3<=AN$22,AN$22<='Travel Worksheet'!$G3),IF(AN$22>='Travel Worksheet'!$M3,'Travel Worksheet'!$N3,'Travel Worksheet'!$L3),0)
AN24	=IF(AND('Travel Worksheet'!$F4<=AN$22,AN$22<='Travel Worksheet'!$G4),IF(AN$22>='Travel Worksheet'!$M4,'Travel Worksheet'!$N4,'Travel Worksheet'!$L4),0)
AO22	=IF(AN22<'Travel Worksheet'!$F$19,Lodging!AN2+1,"")
AO23	=IF(AND('Travel Worksheet'!$F3<=AO$22,AO$22<='Travel Worksheet'!$G3),IF(AO$22>='Travel Worksheet'!$M3,'Travel Worksheet'!$N3,'Travel Worksheet'!$L3),0)
AO24	=IF(AND('Travel Worksheet'!$F4<=AO$22,AO$22<='Travel Worksheet'!$G4),IF(AO$22>='Travel Worksheet'!$M4,'Travel Worksheet'!$N4,'Travel Worksheet'!$L4),0)
AP23	=SUM(AM23:AO23)
AP24	=SUM(AM24:AO24)

I want the dates to start as I said in B2 and terminate at the end date with the formulas in all rows below the dates. After the end date I want a totals column. Please let me know if I can clarify anything and thanks for your help

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>F</th><th>G</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Start</td><td style="text-align: right;;">7/11/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">End</td><td style="text-align: right;;">7/24/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7/13/2011</td><td style="text-align: right;;">7/31/2011</td><td style="text-align: right;;">187</td><td style="text-align: right;;">6/1/2011</td><td style="text-align: right;;">339</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7/15/2011</td><td style="text-align: right;;">8/1/2011</td><td style="text-align: right;;">147</td><td style="text-align: right;;">6/2/2011</td><td style="text-align: right;;">392</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7/20/2011</td><td style="text-align: right;;">7/29/2011</td><td style="text-align: right;;">222</td><td style="text-align: right;;">6/3/2011</td><td style="text-align: right;;">411</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">7/11/2011</td><td style="text-align: right;;">7/12/2011</td><td style="text-align: right;;">7/13/2011</td><td style="text-align: right;;">7/14/2011</td><td style="text-align: right;;">7/15/2011</td><td style="text-align: right;;">7/16/2011</td><td style="text-align: right;;">7/17/2011</td><td style="text-align: right;;">7/18/2011</td><td style="text-align: right;;">7/19/2011</td><td style="text-align: right;;">7/20/2011</td><td style="text-align: right;;">7/21/2011</td><td style="text-align: right;;">7/22/2011</td><td style="text-align: right;;">7/23/2011</td><td style="text-align: right;;">7/24/2011</td><td style=";">Totals</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 339 </td><td style="text-align: right;;"> $ 4,068 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 392 </td><td style="text-align: right;;"> $ 3,920 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ - </td><td style="text-align: right;;"> $ 411 </td><td style="text-align: right;;"> $ 411 </td><td style="text-align: right;;"> $ 411 </td><td style="text-align: right;;"> $ 411 </td><td style="text-align: right;;"> $ 411 </td><td style="text-align: right;;"> $ 2,055 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=Sheet1!B1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">B2<Sheet1!$B2,B2+1,IF(<font color="Red">B2=Sheet1!$B2,"Totals",""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">B$2</font>),IF(<font color="Red">(<font color="Green">B$2>=Sheet1!$F3</font>)*(<font color="Green">B$2<=Sheet1!$G3</font>),IF(<font color="Green">B$2>=Sheet1!$M3,Sheet1!$N3,Sheet1!$L3</font>),0</font>),IF(<font color="Red">B$2="Totals",SUM(<font color="Green">A3:$B3</font>),""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz