Google-sheets – Display Columns For this weeks Data

google sheets

I'm trying to create a to do list schedule like spreadsheet that would show only the data presented for the current week.

I've been having some difficulty figuring out how to display the data of columns for only this week and delete and add columns as days go by. The spreadsheet would display the current date in B1 (3/17/14) Current Day in B2 (Monday) and follow the next six days (to display 3/23/14 in H1 and Sunday in H2). This data would then shift tomorrow to display the new current date in B1 and end of week date in H1 (3/18/14 and 3/24/14) and the corresponding days in B2 and H2 (Tuesday and Monday). The entire columns of the previous B and current H would be deleted and added and everything else would shift over.

I haven't the slightest idea how dates work in Google Scripts and could use any help I can get.

Best Answer

I would suggest something that might not quite meet "delete the values" etc but might serve as giving the appearance of doing so. Use two sheets, one (say RefData) with dates across say Row1 (say starting with March 17 in B1) and cell values such as Exam in say Row3 of the corresponding column. The intention being to have no need to ever delete the contents of this sheet - merely keep extending it to the right.

The other sheet would be very much like the example provided by @Punchlinern, though maybe with =text(B1,"dddd") in B2 (copied across) since WEEKDAY is not necessary, and in say B4 and copied across: 

=offset(RefData!$A$1,2,match(B1,RefData!1:1,0)-1)

This sheet would provide a view of RefData and be populated exclusively by formulae - all data would be entered in RefData.

The optional fourth parameter for offset might be applied if to show more than just a single cell value per day, perhaps a row for each of morning, lunch, afternoon, dinner, evening.

Related Topic