P&L Report (Tool) - Date Issue

Google Sheets
, , ,
1

I had a larger than normal income item hit my transactions tab on 11-01-19. This happened in both the legacy and foundations templates. Both transactions tabs say 11-01, both dashboards must be 11-01 because it appears in the current month numbers, the legacy P&L report tool shows it as 11-01 because it appears in the November column when I run a monthly report, but the foundations P&L report tool from Tiller Labs reports it in the October column when I run a monthly report. Someone at Tiller might want to investigate. I recall prior posts regarding bugs with the dates (time zone issues?) but I thought that was with the Tiller add-on, not the Tiller Labs add-on. Anybody experiencing this issue? Thanks, Blake

2

Thanks for reporting this here @Blake. We’ll take a look and post back when we have a better sense of what’s going on there.

Heather

4

@Blake,

Thanks for flagging this issue. I’m looking into it now. Timezones can be a real headache with scripting because there is the script timezone, the spreadsheet timezone, and the user’s timezone… and it’s not always clear what takes precedence.

Can you answer these questions about the issue so I can reproduce the bug?

  • If you click on the row’s Date in Transactions, does the cell just contain “11/1/2019”? Or is there a time component in the date content?
  • If you go to File/Spreadsheet settings, what timezone is shown?
  • Can you confirm you are running the new P&L report out of Tiller Labs/Tools/Reports/P&L?
  • I’m not sure what you mean by “foundations P&L report”? Are you saying you’re running the Tiller Labs add-on in a Tiller Foundation template?

Thanks,
Randy

5

@randy

  1. cell just contains “11/1/2019”
  2. (GMT-05:00) Eastern Time
  3. Yes.
  4. Yes.
6

Thanks, @Blake. Will look into it.

Randy

7

Changing the time zone for the spreadsheet to (GMT-08:00) Pacific Time fixed the issue.

I had the same problem where all transactions on 11/1 were missing from the P&L report. My spreadsheet was set to (GMT-06:00) Central Time, which is my correct zone. Changing the transaction time to 11/1/2019 2:00:00 also fixes the issue (which is midnight Pacific Time).

I’m not sure if its possible, but you could have the script change the spreadsheet’s time zone to PST before running, then change back.

8

@JD7 , first time posting, and on a holiday. I love it. Thanks.

I changed it to GMT-08:00 and it appears to have fixed my issue. It was affecting all four of my sheets. I reran a P&L report in each sheet and they all look different (accurate) than they did before.

Tiller personnel - Can this be fixed globally for all users or will each user need to change it themselves? I assume this affects all users, right? Why would my sheets say Eastern when I am physically in Pacific? Just trying to understand what is happening here.

Please advise.

Thanks,

Blake

9

@Blake, did you start out with the Foundation Template on all of these or are they a mix?

I’m wondering if it has something to do with the sheet adopting the time zone settings of the owner when a copy is made. I’m the gDrive owner of the Foundation Template that folks use. The “Use Template” feature makes a copy of that sheet i Own. I’m on the East coast so maybe that’s a hint.

Any thoughts @randy?

Heather

10

@heather,

I just created a small fifth sheet a few days ago and it came up with GMT-05:00 so I changed it to GMT-08:00.

If what you are thinking is correct, why would JD7’s be set to GMT-06:00?

I think Tiller is based out of Seattle, right? Thus, the Tiller account might be coded to Pacific. Thus, my thinking goes, all sheets need to be coded to Pacific to work right. Your thought as to how that setting gets changed from Pacific likely has merit.

Hope this helps.

Thanks,

Blake

11

Thanks for running this timezone bug down, @JD7 and @blake. I’ve got it on my list but may not get to it for a few weeks. I appreciate your sharing the workaround with the community.

Randy

12

@Blake, Tiller is based in Seatte, but we’re a distributed team. It looks like the original timezone setting for the Foundation template is Eastern (GMT-5:00).

After a bit of testing, it looks like the “Use Template” or “Make a Copy” option defaults the sheet to the original time zone of the user that owns/created the sheet.

If @JD7 is in the central time zone and started from a brand new/blank Google Sheet then the spreadsheet settings adopted his timezone based on his location when he created the sheet.

So I think the issue is that the Tiller Labs add-on is only using Pacific time (that’s where @randy is based) and the time zone that the add-on is published in so it makes sense that there will always be an offset.

13

Quick update, @Blake and @JD7…

I rebuilt the P&L Report script from scratch. I believe the new version will resolve the timezone issue (no futzing with the spreadsheet timezone required), but it should also improve performance and allows for multi-year reporting (with up to 12 periods).

To give it a try, I’d recommend:

  1. Reload the browser tab with your spreadsheet (to get the latest version of the Tiller Labs add-on).
  2. Unhide the sheet named Report Template: P&L and delete it from your spreadsheet. This sheet is your local copy of the template. When it isn’t present, the latest Tiller master is downloaded into your spreadsheet.

I’m very curious to hear how the new build works for you.

Cheers,
Randy

14

@randy, I appreciate the rebuild. I just ran a 10 month (10 periods) report and it took about 20 seconds. Definitely faster than before.

I have a sheet called PnLTemplate. I assume that relates to the Tiller (not Tiller labs) add-on, right? Delete it too, right?

Curious, how many hours did it take you to do the rebuild from scratch?

Thanks,

Blake

15

That PnLTemplate sheet might be an even older version of the report template from when it was still in the Tiller add-on (with Autocat and the small-business workflows). I think you can delete it. With the (new) Labs report templates, you can modify/personalize them (in some specific ways— I still need to document this)… but, if you ever want to start over, just delete the hidden report template in your spreadsheet and the Tiller Labs add-on will pull down the latest Tiller master again.

It took me about 6 hours on Friday to rewrite the ~500 lines of code for the P&L report script. The version of the P&L report that was originally migrated into the Tiller Labs add-on was ported from and written for the Tiller add-on about two years ago.

Glad to hear the update worked! Thanks for your notes, @Blake.
Randy