Dates in Transactions Sheet Interpreted as Text (not numeric dates)

Ask Anything
1

I have added a “Year” column to my transaction sheet as discussed in this article - Add Year Data to Your Tiller Spreadsheet | Tiller Help Center

Unfortunately, I am getting an error with some of the rows (Function YEAR parameter 1 expects number values. But ‘07/27/2020’ is a text and cannot be coerced to a number.) and the year does not populate. I have double checked the formatting of column B and it is formatted as Date MM/DD/YYYY (not text as the error suggests)

How do I go about correcting this?

Thanks for your help

Screen Shot 2020-12-30 at 8.19.51 PM
Screen Shot 2020-12-30 at 8.19.51 PM3584×2274 903 KB

2

:wave:, @bbuller !

Try changing the year column to plain text format and let me know if that works.

3

I did as suggested but there was no change

4

You can see that the formula is working further down (i.e. around row 270)… so I think the ARRAYFORMULA() is fine. It looks like the issue is with the source-date content. What happens if you retype one of the dates and let Sheets auto-detect it as a date? For example, type “7/31/2020” into cell B257. My guess is that this will fix the value in L257.

If this works, we can think on how to fix the other dates in column B without retyping everything…

5

if I retype it in MM/DD/YYYY format nothing changes however I just tried re-entering the date as YYYY/MM/DD and the array formula updated for that row

6

Bizarre. At the core, the issue is that the way you have configured your spreadsheet, it is interpreting column B as text, not dates. I’m not entirely sure on how to shake it loose.

What happens if you click on the column B header (to select the entire column top to bottom) and then apply a date format. Does that help?

7

I tried that as Heather suggested above - I changed it to plain text and I have tried, automatic and various date formats but it does not seem to actually change the formatting. The only thing that had an impact was for me to manually enter the date in the format of YYYY/MM/DD. The way the transactions come in the from the financial institutions is MM/DD/YYYY

8

If applying formatting isn’t changing the format in column B, then the data is definitely being interpreted as text. Essentially, if it isn’t recognized as a date/number, it doesn’t know how to apply the date formatting.

If it were me, I’d try using DATEVALUE() to convert the text to dates. You could do something like:

  1. In the empty column A, create formula that says =DATEVALUE(B2) in A2
  2. Drag that formula to the bottom of column A
  3. Copy A2:A into the clipboard
  4. Paste the clipboard as values into B2:B
  5. Delete A2:A

If you do this, you should overwrite the text dates with the numeric dates.
Hope this helps.

9

thank you so much for your help!

this sheet really does not like how the financial institutions are populating it.

I did as you suggested and I get the following error (DATEVALUE parameter ‘12/27/2020’ cannot be parsed to date/time.) however this does work for the cells where I manually manipulated the date

10

I had a similar problem in the past, and found that the ‘Locale’ for my spreadsheet was set to somewhere that had a different way of doing dates. To check this, in your Tiller Spreadsheet, go to ‘File’ then ‘Spreadsheet Settings’ and see what your ‘Locale’ is set to. If it’s not where you’re actually at, try changing it and then see how the date formulas interpret the date text.date

3 Likes
11

THANK YOU! THANK YOU!

I had it set to Canada - English (which is where I am). I just tried changing it to United States and all of a sudden it corrected itself.

Thank you so much everyone for your quick responses and insight - I would never have figured this out on my own.

Happy New Year to all!

3 Likes