The Transactions sheet in all Tiller spreadsheets have the "Week" column as a default that is populated by the automated data feeds. Usually you will need to scroll to the right in your Transactions sheet to see it.
You might need to manually add week data if you're preparing historical data that you want to manually add to your Transactions sheet, or inputting transactions for accounts Tiller can't automate.
Adding week data in Google Sheets
You can use the below arrayformula to have it automatically fill in the week data for the data set in a "Week Array" column. We're not using this formula in the existing Week column because that would break Tiller's automated Week column.
Click into the first empty cell in row 1 to the far right or insert a column to the left of the existing Week column.
Paste this formula into the header row (row 1)
This assumes the "Date" is in column B (the default in the Foundation template). Change the formula to match the location of the date data in your prep sheet.
=arrayformula(if(row(B1:B)=1,"Week Array",if(B1:B<>"",B1:B-WEEKDAY(B1:B)+1,"")))
Copy the week dates (right click > Copy or CTRL+C/CMD+C) from this new column for the transactions where the week data is missing. Usually this is for historical transactions you've manually added.
Right click into the first cell in the Week column that corresponds to the transaction where you need to add the week data.
Choose Paste Special > Values Only to paste the week data into the Week column for the transactions where the month data is missing.
Delete the helper column you added out to the right. Tiller automatically populates the week data for transactions it fills in so there is no need for the duplicate week data column.
Adding week data in Microsoft Excel
The Tiller Foundation Template Transactions sheet is set up as an Excel Table so the formula used below automatically fills to all empty cells in the table boundary in the column where you add it in row 2. These steps will only work if your Transactions sheet is set up as an Excel Table.
To verify that your Transactions sheet is set up as an Excel Table,
Click into the Date cell in row 2
Review the very top of your Excel workbook and look for either "Table" or "Table Design" ribbon that will appear to the right of the View ribbon.
If your Transactions sheet is not set up as an Excel Table click here to get the steps on how to do that.
Adding Week Data to your Transactions sheet
Click into the first far right empty cell in row 2. This is usually to the right of the Date Added column, but this could vary depending on whether you've added your own custom columns. The column header should be blank and the column will not be in the Excel Table boundary yet.
Click into the formula bar at the top
Paste in this formula:
=A2-MOD(A2-1,7)
Format the column as a Short Date. Usually when you paste into an empty column in Excel the default format will not be a date.
Copy the week dates from this new column for the transactions where the week data is missing. Usually this is for historical transactions you've manually added.
Right click into the first cell in the Week column that corresponds to the transaction where you need to add the week data.
Choose Paste Special > Values to paste the week data into the Week column for the transactions where the week only where data is missing.
Delete the helper column you added out to the right. Tiller automatically populates the week data for transactions it fills in so there is no need for the duplicate week data column.
