I’d love if the date/time stamp for my balance history entries was in my local time.
Hi @heather. Here’s a way to get the date/time stamp in your local time.
Insert a new column in your Balance History sheet.
Select the new column and adjust the format to Date Time by clicking on “Format” -> “Number” -> “Date time” so all the cells will use the format m/d/y h:m:s.
Copy (and adjust if needed) this formula into the top cell of the new column:
This formula assumes the Date column is column B and you are in Eastern time, 3 hours ahead of Pacific Time. But you can adjust it to whatever time zone you need.
ARRAYFORMULA()is used so all the rows in the cell use the following formula.
Change the 2
B:Breferences to the column letter of your Date column, if Date is NOT in column B.
In the top row,
B1 = "Date", so the new column will display “Local Time” in that row.
B:B+TIME(3,0,0)takes the date including the hidden time from Column B, (Tiller uses Pacific Time) and adds 3 hours to it using the
TIME()function. You can change the formula by adjusting +TIME to -TIME and changing the 3 to the number of hours offset you need from Pacific Time.
If you don’t want to use the standard m/d/yy h:m:s format, you can create a custom format with Google’s Date format options.
Let us know if that works for you.
I have always been frustrated with the date and time in the Balance History tab not being local. I did what you mentioned. I inserted a blank column to the left of the date column. This shifted my date column from column B to column C. I pasted the formula into cell B1 and changed the B’s to C’s and changed the 3 to 0 since I am on Pacific time. My results in column B are a date that equals the date in column C and the times all say 0:00:00. Did I do something wrong? Please let me know. Thanks.
I believe there are 2 different versions of the Balance History sheet and that might be the problem.
In the pre Tiller Money Feeds version, which was used until recently, in the Balance History sheet, Column A is the Date in mm/yy/dddd format and Column B is the Time in hh:mm AM/PM format. At least this is the format in my old sheets.
My Date and Time are in GMT time, which is now 7 hours ahead of Pacific Time.
@benlcollins points out its important when using Google Sheet to know:
spreadsheets store dates as 5-digit numbers. For example, today 9/13/19 is stored as the number 43721 (change the formatting to a number to see).
To convert a new column to Pacific Time, I use the formula:
=arrayformula(if(A:A="Date","Local Date",to_date('Balance History'!A:A + 'Balance History'!B:B) - 0.29166))
A full day has 24 hours. Since Pacific Time is now 7 hours ahead, that’s 7/24 or a day difference or 0.29166 of a day. That’s why I subtract that fraction to the Date and Time field to get the correct Pacific Time.
If you are not in Pacific time, calculate a different fractional offset.
When the daily savings time time changes happen, you would need to adjust the formula for an 8 hour offset. 8/24 = 0.333333.
I suppose if you want to get really fancy, you could include something in the formula to adjust for the time zone changes automatically. Anyone want to try that?
Make sure the new column is set to the date formating style you want. If it’s set to Number, you will see a plain number, not a date.
In the new Tiller Feeds Balance History sheet, the Date column is already in Pacific Time. So if you are in Pacific Time, you won’t need to convert. But if you are not in Pacific Time, you can use the solution earlier in this post.
Let us know if that works for you.
I made it work. Here is what it looks like now. The formulae are shown below. I will hide columns D and E and then I will be set. Thanks!
=arrayformula(if(D:D=“Date (GMT)”,“Date (PT)”,to_date(‘Balance History’!D:D + ‘Balance History’!E:E) - 0.29166))
=arrayformula(if(D:D=“Date (GMT)”,“Time (PT)”,to_date(‘Balance History’!D:D + ‘Balance History’!E:E) - 0.29166))