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? Is the data correct, but in the format wrong? Or is the data wrong?