Changing Date Formats for non-US users

What is the goal of your workflow? What problem does it solve, or how does it help you?

I’m a new Tiller spreadsheet user, but I’m based in Australia. I was having trouble with the fact that I’d download my transaction data from the bank and Tiller would read the dates incorrectly because the template & its formulas use American dates.

How did you come up with the idea for your workflow?

I wanted to find a workaround that wouldn’t require me to have to manually correct every single date for every single transaction from now until eternity.

I asked for some guidance for the incredibly helpful support team. But I realised that I’d come up with a better solution than anything they offered. So I figured I’d share it with you!

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

I only added 1 additional column (to the right of the ‘Dates’) in the Transactions sheet.

I labelled the new column ‘DD/MM’, but you can label it anything you want.

Do not change the name of Column B. This is important because the formulas in other sheets rely on the existing ‘Dates’ column (Column B) using the name ‘Dates.’ You need to make sure you leave that name as is, and have a unique name for the new Column C.

Then for Column B, I added this formula:

=DATE(RIGHT(C8, 4), MID(C8, 4, 2), LEFT(C8, 2))

This takes the DD/MM/YYY dates and translates them into MM/DD/YYYY.

Then for the new DD/MM column (now Column C), I can simply copy/paste the transaction data from my bank and the rest is automatic and doesn’t affect formulas in other sheets.

Anything else you’d like people to know?
You need to make sure that Column C (DD/MM, or whatever you label it) uses two digits for the day and two digits for the month eg. 08/04/2021. It won’t work if it says 8/4/2021.

To set this up, highlight Column C. Go to Format in the menu bar, then down to numbers, then “more formats,” then over to “More date and time formats”. (Hopefully the screenshot below helps.) Then simply make sure there’s a 0 in front of the Day and Month digits.

Does that help? It works better for me than any other methods I found.

Hi @alyssa …very clever, indeed! Do you then hide column B and you mentioned pasting transactions. Newly downloaded transactions are also transposed, too?

I think you meant to say “column C” here?

I don’t totally understand what you mean here. Are you copying/pasting data from your bank in to column B the “Date” column or isn’t column B populated by Tiller?

Also, do you have to add the formula to reformat the date to each individual cell / transaction? E.g. it doesn’t populate down automatically?

1 Like

It’s up to you whether you want to hide Column B. Personally, I don’t. I like to see that everything is working properly and that the DD/MM dates have correctly converted in MM/DD dates. Maybe when I’m more confident I’ll hide Column B.

Nope @heather, the formula goes in Column B as written in the instructions.

I’m not pasting any bank data into Column B. Only the formula goes in there.

I’m pasting my transaction data from the bank into Columns C, D and E. Then the formula automatically translates that DD/MM/YYYY dates I’ve pasted into Column C into MM/DD/YYYY dates in Column B.

Yes, you need to pull the formula down to all the cells in the column. I haven’t figured out a way to make the formula work for the entire column yet. Fingers crossed someone else will have the expertise to help with that part :crossed_fingers:

Hi @alyssa

Here’s one option for your new column C that will populate the column automatically with the dates in column B, but with a new Australia format. This should also transform dates for new transactions going forward.

  1. Create a new column C
  2. Add this formula in the first (header) row

=Arrayformula(IF(Row(C:C)=1,“MM/DD”,$B:$B)).

(Of course, you can replace MM/DD with another name of your choice.)

  1. Format the new column C with the modified DATE/TIME option of your choice.

This creates an automated transformation of your date data in a format you can use.

Will that work?

OMG yay @Brad.warren. I’ve had so much trouble getting an array formula to work. I’ll give this one a go! Thank you x a million.

I’ve played with this formula, but it unfortunately doesn’t work @Brad.warren. I suspect it’s because the formula assumes that Tiller will automatically give us dates in Column B. But it doesn’t do that yet for most of us outside the US.

The advice I received from Customer Support was that Tiller works with many institutions outside of the US. However, support for non-US banks isn’t as widespread. (They have a Google Sheet of Tiller Money-Compatible Institutions here.)

For any non-Americans reading this, like me, you’re potentially stuck with just the Google Sheets template and a manual workflow for now. We can’t get full access to Tiller and all its fancy automations yet, but they’re working on it.

I’m finding the Foundations Template good enough on its own to make it worth the wait… especially once sorting the date formatting issue out.

What column holds the dates of your transactions?

Column C is where I paste the date data from the bank. Then Column B transcribes it into MM/DD format.

So does this mean you’re manually adding all your transaction data? Tiller does give date data for transactions where we can automate.

Yes.

How does Tiller give date data for non-Americans? This is vastly different to the advice I was given from customer service.

Hi @alyssa:

When you have imported the transactions in the past, what do the data look like in the date column of the Transactions Sheet? Are the data correct, but in the format wrong? Or are the data wrong?

hi @alyssa,

First, perhaps there is a misunderstanding. We’re only officially registered in the US so that’s our focus, but our data provider supports institutions from around the world. Did you try connecting any accounts to Tiller? We actually have quite a few people from Australia that use Tiller with their banks successfully.

Second, the date data in the sheet is all based on how the column is formatted. The format is US date format because that’s our primary/supported market. The templates are built for US dates. But you could technically change the format of the Date column in the Transactions sheet to be whatever you want. It’s just that the downstream “visualizations” will likely break, but that doesn’t prevent you from building your own sheets or pivot tables that use a different format.

Does that help clarify?

Hi all,
The solution is extremely simple, but it took me months to realise it because of a lack of familiarity with Excel and because I mostly work in Python.

  1. Create a column at the right-most of the sheet and rename it to Date (Australia) if you like.
  2. Copy this formula to the second row (first row after the header):
    =arrayformula(if(B2:B=“”,“”,text(datevalue(B2:B), “dd/mm/yyyy”)))

If you are doing this in Excel and not in Google Sheets, you can use the following formula: =IF(B2:B=“”,“”,TEXT(DATEVALUE(B2:B),“dd/mm/yyyy”))

Here, we are assuming that B2 is the cell having your first entry of US-format date. The arrayformula() applies this to all the rows below as well as any new rows that load into the sheet.

4 Likes

[Disclaimer: Apologies if my tone doesn’t land right below – I honestly don’t mean any offence, and this is not aimed personally at any posters, commenters, staff or community members; this is feedback intended for Tiller in terms of marketing and customer support. I expect they don’t have the resources to implement the things I’d like to see, and I wouldn’t expect volunteer members of a community who live and work in an entirely American context to feel they should support those of us out here across an ocean or two. BUT…]

It’s genuinely lovely that people are supporting each other with good attempts at workarounds, even if there does seem to be quite a lot of misunderstanding in the thread above about what direction the conversions are wanted to happen in. I’m in NZ, originally from the UK, although I do have one US bank account that will talk to Tiller. Unfortunately (and I guess this is an aspect of my flavour of being autistic flaring up here) I simply can’t tolerate seeing dates ‘backwards’ everywhere (i.e. in US format). I totally appreciate (now) that this is a US-centric product, but if I’d anticipated that this would be an issue I never would have signed up. In all honesty, for me the mix of dates, and dominance of the ‘wrong’ format (from my perspective) is just unbearable as a user experience and makes it near impossible for me to process the information I see without a great deal of cognitive effort I can’t afford.

The fact that the problem has been hard-coded throughout the core system and many extensions is understandable for a product nobody expected to be used outside of North America, but given that if working from the ground up it’s not too difficult to build something that can use flexible date formats (particularly on spreadsheet platforms that natively use numerical date fields that aren’t explicitly formatted) it’s pretty disappointing. I’m glad (for them) that some people can tolerate clunky workarounds for the sake of accessing the other functionality, but it wouldn’t surprise me if a lot of other potential international customers are put off from using Tiller because of the formatting problem. It’s a shame, as it looks great and I was looking forward to exploring what folk have been doing with it. The disappointment reminds me of when I encounter things that assume I wan’t to use feet and inches, or non-ISO paper formats, or insist on giving temperatures in Fahrenheit.

Somewhat ironically I do have the coding/Google Sheets skills to ‘fix’ many of the problems, at least cosmetically, but I came to Tiller looking for an easy-to-tweak set of modular templates and I don’t have the time or energy to go re-engineering a product that’s not really been built for an international market. I read somewhere that it’s OK to keep copies of downloaded templates for personal use as a non-customer, so I’ll hold on to some of them to possibly play with in future if I have any free time–but I can’t justify a paid subscription when I’m going to have to do all the legwork myself to fix a (contextually) broken system. And suggesting that I just shrug off the issue and put up with the US-style dates doesn’t fix my problem, because I can’t.

If at all possible, I’d request that Tiller make it much clearer in their online presence/marketing that the dates system is currently internationally incompatible, as I feel it was mis-sold to me through omission of this information. I’m still in my trial period, so I’ll need to make sure I cancel the account as soon as possible as I don’t want to get stuck having paid for something that for me (as it stands) is entirely unusable. (Because, haha, I’m one of those people who double-up their autism with ADHD, so have fallen victim to the ‘sign up for free trial but give us your credit card details first’ pitfall many times in the past. I nearly balked from creating a Tiller account because of that, as it always feels like a scam designed to prey on the congenitally disorganised, but I broke my own rule. I wish I hadn’t.)

I guess I just didn’t do enough research before signing up, so more fool me. This is very tiring–and just one instance of the much broader problem of US-centric virtual systems generally, when they pop up in English language searches or sources. I entirely understand that US companies and communities have no obligation to the rest of us–it’s just frustrating when we get their products pushed in our online direction without clarity as to their geographical or cultural exclusivity.

Hope this hasn’t come across as a rant. I’m honestly not angry; just rather sad to miss out on using what looks on the surface to be quite a good set of tools. If you’ve succeeded in reading this far, you surely deserve a small reward–or something more interesting to do–so I’m very much hoping you find it.

Look after yourselves folks. This is totally about the Thing, not the People. I certainly don’t want to go around adding fuel to the fires of ire by bashing Americans, especially when they’re so deeply in the midst of calamitously bashing each other. Peace and love to all. I do hope things improve.

[ahhh… ETA-- oh… no… I said ‘autistic’ in a thread about US/everywhere-else disconnect. I. Am. So. Sorry. No, really.]

this is great feedback, @LearnWithChris and I’ll definitely pass it on to our marketing team. I totally understand your frustration here and we definitely do hope to eventually support more dates and currencies!