How to calculate "Month" and "Week" columns in Transaction sheet?

I imported my bank and credit card transactions to Tiller and need to include columns “Month” example: (2019-05-01) and “Week” example: (2019-05-14).

Does anyone know how to do this based on the transaction “Date” column? I can’t tell how Tiller is doing it in the template.

If the transactions come in automatically, Tiller puts the dates in automatically. If you bring in and/or make transactions manually (which is what I assume you mean by import), then you need to enter the dates yourself.

The date in the Month column is always the first day of the month. So, if the transaction date is 05-01-19 through 05-31-19, then the Month column date is 05-01-19.

The date in the Week column is the day of the week beginning on Sunday. So, if the transaction date is 09-18-19, then the Week column date is 09-15-19. So, if your transaction date is 09-15-19 through 09-21-19, then your Week column date is 09-15-19.

Thanks, Blake

1 Like

Hi @hbwilliams22,

You can have an arrayformula help you with calculating the month/week data for your manually imported transactions.

The best workflow is to prep the month/week data in a separate tab for the manual entries and then copy the data then right click > paste special > values only back into your Transactions sheet.

This ensures that Tiller can automate the data for month/week going forward rather than having an arrayformula do it in your Transactions sheet, which can slow it down over time.

2 Likes

Hi Heather!

I´m trying to use the array formula on the prep sheet to add month and week dates, but I´m getting the following #REF! message: “The result was not expanded automatically, unsert more lines” although the sheet has all the free lines bellow the cell. Would you be able to shed some light?

Thank you!

Were you inserting the formula in row 1, @alexvpp? Is it possible any of the cells below the formula have data in them? I’d select to the bottom of the sheet and click delete.

1 Like

Hey Randy!
You got it.
I was inserting the formula in row 2…
Justr changed B1 values to B2 and it worked fine.
Thank you!

I am using Microsoft Excel, and I’m importing legacy transaction data for the current year to get started with Tiller, and face the same problem, to populate the Month and Week columns correctly based on the imported Date column. So far as I can tell, it works correctly to use these simple Microsoft Excel formulas in a temporary column:

Month: =EOMONTH(A2,-1)+1
Week: =A2-WEEKDAY(A2,2)

Then copy the temporary columns, insert two new columns, and use Excel Paste Special to paste just the values into the new columns. Then just delete the two temporary columns, and you have simple Month and Week column values for the manually entered data rows.

This assumes the Date column is column A, and that the first data row is row 2. You can then extend these formulae down their columns to the end of the data rows, and Excel will increment the row numbers appropriately for each row.

The Month formula works by using the builtin EOMONTH to get the date for the last day of the previous month, and then adds 1 to it.

The Week formula works by usin the builtin WEEKDAY function to figure out the weekday number for the given date, where Sunday is 0, Monday is 1, Tuesday is 2, etc. It subtracts the weekday number from the given date to get the date for the previous Sunday, or the given date if it falls on Sunday.

1 Like

I have recreated my workbook from the Excel Tiller Foundation Template, and now my weeks for data pulled in by the Money Feeds uses Monday as the first day of the week, since I have it set that way in Windows Settings. I’m pretty sure that my previous workbook created from the foundation template used Sunday as the first day of the week, regardless of my Windows Settings. Anyway I am now using the following Week formula for my manually added data:

Week: =A2-WEEKDAY(A2,2)+1

After adding a temp column with this formula, and then copying the values to my Week column, my Week values for Manually entered data are now consistent with my Week values added by the Money Feeds add-in, after I inserted the migrated mint transactions data rows into the middle of the Transactions table in my new workbook created from the foundation template.

Regards, Robert