excelIsland
New Member
- Joined
- Feb 3, 2012
- Messages
- 45
Hi,
I have a scenario where I have potentially 12 quarters of data (say 2012Q1 - 2014Q4) but the user puts in a start date and end date.
For my example lets say I have -
Start Date - 2012Q3
End Date - 2013Q3
<table width="945" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl91" style="height:12.75pt;width:61pt" width="81" height="17">
</td> <td class="xl91" style="width:62pt" width="82">
</td> <td class="xl91" style="width:61pt" width="81">Start</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:59pt" width="79">
</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:62pt" width="82">End</td> <td class="xl91" style="width:56pt" width="74">
</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:58pt" width="77">
</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:58pt" width="77">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl92" style="height:12.75pt" height="17">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl93" style="border-top:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl89" style="height:12.75pt" height="17">2012Q1</td> <td class="xl89" style="border-left:none">2012Q2</td> <td class="xl89" style="border-left:none">2012Q3</td> <td class="xl89" style="border-left:none">2012Q4</td> <td class="xl89" style="border-left:none">2013Q1</td> <td class="xl89" style="border-left:none">2013Q2</td> <td class="xl89" style="border-left:none">2013Q3</td> <td class="xl89" style="border-left:none">2013Q4</td> <td class="xl89" style="border-left:none">2014Q1</td> <td class="xl89" style="border-left:none">2014Q2</td> <td class="xl89" style="border-left:none">2014Q3</td> <td class="xl89" style="border-top:none;border-left:none">2014Q4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl90" style="height:12.75pt;border-top:none" align="right" height="17">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$190,968</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$190,968</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$190,968</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$203,816</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$203,816</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$203,816</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> </tr> </tbody></table>
How best can I then display -
Contract Year 1 as a sum of 2012Q3 to 2013Q2
Contract Year 2 as a sum of 2013Q3 to 2013Q3
Contract Year 3 as 0 since it never goes into 9 QTR of data.
This obviously being dynamic in such when the dates change the sums adjusts. I have IF statements for the Start and End to be placed in the row above if that even matters.
Thanks
I have a scenario where I have potentially 12 quarters of data (say 2012Q1 - 2014Q4) but the user puts in a start date and end date.
For my example lets say I have -
Start Date - 2012Q3
End Date - 2013Q3
<table width="945" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl91" style="height:12.75pt;width:61pt" width="81" height="17">
</td> <td class="xl91" style="width:62pt" width="82">
</td> <td class="xl91" style="width:61pt" width="81">Start</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:59pt" width="79">
</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:62pt" width="82">End</td> <td class="xl91" style="width:56pt" width="74">
</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:58pt" width="77">
</td> <td class="xl91" style="width:59pt" width="78">
</td> <td class="xl91" style="width:58pt" width="77">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl92" style="height:12.75pt" height="17">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl92">
</td> <td class="xl93" style="border-top:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl89" style="height:12.75pt" height="17">2012Q1</td> <td class="xl89" style="border-left:none">2012Q2</td> <td class="xl89" style="border-left:none">2012Q3</td> <td class="xl89" style="border-left:none">2012Q4</td> <td class="xl89" style="border-left:none">2013Q1</td> <td class="xl89" style="border-left:none">2013Q2</td> <td class="xl89" style="border-left:none">2013Q3</td> <td class="xl89" style="border-left:none">2013Q4</td> <td class="xl89" style="border-left:none">2014Q1</td> <td class="xl89" style="border-left:none">2014Q2</td> <td class="xl89" style="border-left:none">2014Q3</td> <td class="xl89" style="border-top:none;border-left:none">2014Q4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl90" style="height:12.75pt;border-top:none" align="right" height="17">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$190,968</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$190,968</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$190,968</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$203,816</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$203,816</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$203,816</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> <td class="xl90" style="border-top:none;border-left:none" align="right">$0</td> </tr> </tbody></table>
How best can I then display -
Contract Year 1 as a sum of 2012Q3 to 2013Q2
Contract Year 2 as a sum of 2013Q3 to 2013Q3
Contract Year 3 as 0 since it never goes into 9 QTR of data.
This obviously being dynamic in such when the dates change the sums adjusts. I have IF statements for the Start and End to be placed in the row above if that even matters.
Thanks