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.