Changing Date Formats for non-US users

Google Sheets Show & Tell
, , ,
1

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.

Screen Shot 2021-11-09 at 1.51.58 pm
Screen Shot 2021-11-09 at 1.51.58 pm1556×186 16 KB

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.

Screen Shot 2021-11-09 at 2.07.54 pm
Screen Shot 2021-11-09 at 2.07.54 pm1920×1200 186 KB

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

2

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

3

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
4

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.

5

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:

6

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?

7

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.

8

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.

9

What column holds the dates of your transactions?

10

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

11

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

12

Yes.

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

13

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?

14

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?

15

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