date math

[deedee88]

Hi,

I have a worksheet with date amounts in each column for each day of the year, the rows are location numbers. How can I pull up to another spreadsheet todays date amount and week to date amount, month to date amount, quarter to date amount and ytd amount without having to go into the cell and change the formula manually each time. My company is under a fiscal calendar 4,4,5 week periods and the week period runs from Sunday to Saturday.

Any help will be appreciated!

Thanks,

 

[Claymationator]

This shouldn't be too difficult to do, however can you post a sample of your data so that we can get is all figured with your current layout?

 

[deedee88]

<table width="955" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 46pt;" width="61"> <col style="width: 72pt;" width="96"> <col style="width: 59pt;" width="79"> <col style="width: 67pt;" width="89"> <col style="width: 78pt;" width="104"> <col style="width: 57pt;" width="76" span="2"> <col style="width: 62pt;" width="83"> <col style="width: 68pt;" width="91"> <col style="width: 51pt;" width="68" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td colspan="2" style="width: 118pt;" width="157">Plan/Forecast</td> <td style="width: 59pt;" width="79">
</td> <td style="width: 67pt;" width="89">
</td> <td style="width: 78pt;" width="104">
</td> <td style="width: 57pt;" width="76">
</td> <td style="width: 57pt;" width="76">
</td> <td style="width: 62pt;" width="83">
</td> <td style="width: 68pt;" width="91">
</td> <td style="width: 51pt;" width="68">
</td> <td style="width: 51pt;" width="68">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Sunday</td> <td>Monday</td> <td>Tuesday</td> <td>Wednesday</td> <td>Thursday</td> <td>Friday</td> <td>Saturday</td> <td>Sunday</td> <td>Monday</td> <td>Tuesday</td> <td>Wednesday</td> </tr> <tr style="height: 25.5pt;" height="34"> <td style="height: 25.5pt;" height="34">Location</td> <td class="xl65" style="width: 46pt;" width="61" align="right">1/2/2011</td> <td class="xl65" style="width: 72pt;" width="96" align="right">1/3/2011</td> <td class="xl65" style="width: 59pt;" width="79" align="right">1/4/2011</td> <td class="xl65" style="width: 67pt;" width="89" align="right">1/5/2011</td> <td class="xl65" style="width: 78pt;" width="104" align="right">1/6/2011</td> <td class="xl65" style="width: 57pt;" width="76" align="right">1/7/2011</td> <td class="xl65" style="width: 57pt;" width="76" align="right">1/8/2011</td> <td class="xl65" style="width: 62pt;" width="83" align="right">1/9/2011</td> <td class="xl65" style="width: 68pt;" width="91" align="right">1/10/2011</td> <td class="xl65" style="width: 51pt;" width="68" align="right">1/11/2011</td> <td class="xl65" style="width: 51pt;" width="68" align="right">1/12/2011</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">5</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> <td align="right">200</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td colspan="6" style="">so is today's day is 1/10/2011 I would like to see the following summary</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Day</td> <td>WTD</td> <td>MTD</td> <td>etc</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">200</td> <td align="right">400</td> <td align="right">1100</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[stefankemp]

For day: =HLOOKUP(INT(NOW()),Data!$A$3:$ZZ$200,row($A2),0).

This assumes your table is stored in a sheet named "Data", with the dates in row 3 and location in column A.

This will give you the day amount for location 1. You can drag this formula down to give you amounts for locations 2,3,...

 

[stefankemp]

For MTD: =SUMPRODUCT((MONTH(Data!$B$3:$ZZ$3)=MONTH(NOW()))*(DAY(Data!$B$3:$ZZ$3)<=DAY(NOW()))*Data!$B4:$ZZ4).

Again you can drag this down to give totals for other locations.

 

[stefankemp]

For YTD: =SUMPRODUCT((Data!$B$3:$ZZ$3)<=NOW())*Data!$B4:$ZZ4).

 

[stefankemp]

For WTD: =SUMPRODUCT((WEEKNUM(Data!$B$3:$ZZ$3,1)=WEEKNUM(NOW(),1))*Data!$B4:$ZZ4).

In all my replies I have used a range from column A or B to ZZ. You should change it depending on the range you have entered the days of the year in.

 

[deedee88]

Thanks, I will give it a try!