Date format should allow for non-US customers

Hi, in the Tiller templates, please allow for dates to be non-United States format. US format is mm/dd/yy while the rest of the world is dd/mm/yy. Tiller customers who are not in the United States would prefer the dd/mm/yy format. Thank you.

Hi @brian,
If you select the date column, then Format -> Number -> More Formats -> Custom number format then you can adjust the date into any format you like, including dd/mm/yy.

Let us know if that works for you.

Hi,

Thanks for getting back to me, but this did not help. Several points:

  • Heather from Tiller told me: "Though if you’re using one of our templates this will likely break some features of the template as they’re built for US at this time. " I didn’t get a response from her about the details of her comment. Like what those broken features would be.

  • When I enter a date into my newly formatted (dd/mm/yy) column, Google (Tiller?) swaps the month and year on the fly! so sept. 7 becomes July 9! This happens silently and I didn’t even notice until later when stuff wasn’t adding up.

  • Separately (but I think related) is that the timezone seems to be US-based. I have transactions that I know took place on certain days, but the transactions are showing as coming through as a day earlier (!)

Sorry, but I don’t think Tiller is working out for me for the above reasons. There is good promise with Tiller, but the US-centric view is too much to deal with. I wish you luck.

Of course, if I am missing some obvious fix, I’d be happy to listen.

Cheers,

Brian

Hi @brian,
Sorry to hear that solution didn’t work. Since i’m in the US, it’s hard to test a non-US solution.

I still think it may be possible to fix this and get it to work for you, if you are willing to give it a try.

I’d like to understand why the date and months are getting mixed up. First, revert the original Date column in your Transactions sheet to the original format. Insert 2 additional columns on the right of your Transactions sheet.

Put the formula
=iferror(arrayformula(MONTH(B:B)),"MONTH")
in the top row of one column and

=iferror(arrayformula(DAY(B:B)),"DAY")
in the top row of the other one.

Do they correctly display the Month and Days? Or is it backwards?

Once we get that sorted out, we can use the technique I wrote about here to adjust the time of the transactions so they appear on the correct date for you. See: Date/time in balance history in local time

I think if we put a separate Adjusted Date column into your transactions sheet, that will reduce the risk that other features would break.

1 Like

Hi,

Attached is a screen grab of those two new columns. I don’t know what the correct values should be, so I can’t comment on the first columns values b/c both numbers are less than 13. The second column looks “okay” except only a few rows have an actual date value. I don’t know if that’s an issue or not.

I looked at your workaround for the adjusted date column, thanks.

Cheers,

Brian

tiller.PNG

Thanks for sending the screen grab and giving this a try. We are getting closer to figuring it out.

Those columns P and Q are formatted with a mm-dd-yy or dd-mm-yy format. But to understand what’s happening, they need to be just a simple number format to show the month and day number.

Select the columns, then Format -> Number -> Number. If those results make sense, we might be able to use the DATE(year,month,day) function to generate an adjusted date for your non-US location.

Hi,

Ok, I tried exactly as you said, and the P column is filled with “MONTH” and the Q column is filled with “DAY”

No numbers or dates in either column, Just those character strings.

Cheers,

Brian

Thanks for continuing to test this. I think i see the problem.

I gave you the formula:
=iferror(arrayformula(MONTH(D:D)),"MONTH")
and
=iferror(arrayformula(DAY(D:D)),"DAY")

My test sheet was using the Date in Column D. But you are likely not using Column D for the original date. Perhaps its Column B instead?

Change the D:D in both formulas above to the letter of your original Date column. Then the numbers should fall into place. Hopefully!

Is your original date column D? That’s how it is in the template i’m using, so I assumed it was in your sheet.

If the original t

Hi,

Yes, my Date column is Column B. I changed the formulas as you suggested and everything worked as expected. August came through as 8.0 and the 23rd came through as 23.0.

Cheers,

Brian

OK. That’s good.

I think you should be able to now create an adjusted date column in the style you want. Keep the original column and use that to generate the new column. Hopefully, this approach will solve the problems that just adjusting the formatting using the formatting tab created.

For example, to get a DD/MM/YYYY format for a date in B2:
=DAY(B2)&"/"&MONTH(B2)&"/"&YEAR(B2)

To make this work on all the rows in the column, use this if the original date is in Column B:
=IFERROR(ARRAYFORMULA(DAY(B:B)&"/"&MONTH(B:B)&"/"&YEAR(B:B)),"ADJUSTED DATE")

This column can likely be set to Format -> Number -> Automatic with Google Sheet’s formatting.

Let us know if that works for you.

Hi,

Yes, your suggestion works fine. Thanks for the help.

cheers,

Brian

I’m glad to hear this! Thanks.