What would cause the value of the Week data in the Transaction and Balance History tables to be set to the first Monday instead of Sunday? I’m specifically referring to when running the Tiller Money Feeds update (not when manually adding Week Data).
My understanding is the first day of the week value is set by default to Sunday in both Excel and Sheets. I’m using Office 365 installed on Windows 10. The Regional Format Data is set correctly. The Week column in both the Transaction and Balance History tables are formatted with *3/14/2012 date format. Yet, the Week date is being set to the first Monday.
To eliminate any potential issue with my Tiller template, I tested this out with a new Tiller template and the issue exists in this scenario too.
Any help or insight by an Excel or Tiller Money Feeds expert would be greatly appreciated.
Does it happen always or just sometimes?
If sometimes, do you see any pattern/differences with those that work as expected?
This initially makes me think of time zone differences, like the Date recorded in Balance History is UTC time zone (independent of spreadsheet time zone settings) and so a time late in the day US time would be the next day UTC time.
Yes. The TIME column on the Balance History tab both Excel and Sheets is about 3 hours later than when the data was manually filled. I noticed this yesterday and it was the same today.
In my Excel Tiller template, on the Balance History tab, Date contains date & time values and the time appears to be UTC. The Date column is formatted as *3/14/2012 date format. The Time column contains date & time and is formatted as General.
In my Sheets Tiller template, on the Balance History tab, Date only contains date and time only contains time. Time appears to be UTC. Both columns are formatted with Automatic.
I’m not sure if any of this formatting matters as the underlying number values are being calculated by the Tiller Money Feeds, I assume.
Sheets is populating the day of the week correctly. Excel is not.
I checked my Google Sheets Week column and it’s all good.
I created a new Excel Tiller template and tested it by linking an account and it behaves as you report - Transactions and Balance History tabs have all Week dates of Monday, instead of the intended Sunday.
Kinda looks like a bug with the Excel Tiller filled data.
Just curious - do you have anything that depends on the Week column?
I have both a Google Sheets and Excel foundation template and can verify that I see the same thing. For the exact same transaction, Week is set to date of the Sunday in Sheets but the date of the Monday in Excel. It seems like the Tiller feed may be calculating them differently?
To @Mark.S point, I’m not sure what is dependent on it. I looked at Spending Trends for example and when you select Insights for: This Week or Last Week, it gets the dates right. There must be something dependent upon the Week column, but I’m not sure what it is. If you follow the Tiller instructions for adding the Week value manually using the formula =A2-MOD(A2-1,7) you’ll get the date corresponding with Sunday.
I don’t have anything that depends on it. I only noticed the discrepancy after setting up a Google Sheets template and validating my setup with my Excel template.
The only reference I found in the help guides was for use with pivot tables in the Transactions Sheet Columns guide.