1

I am trying to create an annotated timeline with the following data, which exists on another sheet:

My sample data

I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated timeline chart, but I keep getting this error:

Each values column may be followed by one or two annotation columns. column number 1 is of type string

The error I am getting

I have tried

  • (Select Column) Format->Number->Date and

  • (Select Column)Data->Data Validation->Must be valid date

It is driving me nuts. Is it because it is an ARRAYFORMULA? If I hand type it out again (which is simply untenable) the chart generates fine. Is there a fix, or is this just broken?

Google Sheets link:

https://docs.google.com/spreadsheets/d/1sIEgWVPLrNWYImCIlWS8gvj8JQIe-cB4P2grlHW0ysk/edit?usp=sharing

On "Sheet20", you will see the failing version, and if you scroll right you will see the identical hand typed data on a working version.

  • 1
    your issue is in column B arrayformula formating, not in array of A column – user0 Jan 16 at 5:41
0

Yes they can, but the whole array needs to be converted back into VALUE()

B1: =ARRAYFORMULA(IF(LEN(A1:A),VALUE(SUBSTITUTE(Patches!B1:B1000, ".", "", 2)), ))

3

  • Can you explain what was causing this? I don't understand what you mean by back into __VALUE ()__. Did I accidentally convert something or was it a byproduct of ARRAYFORMULA ()? – user205834 Jan 16 at 12:32
  • @TylerGubala your formal formula in Sheet20!B1 was =ARRAYFORMULA(SUBSTITUTE(Patches!B1:B1000, ".", "", 2)) – user0 Jan 16 at 12:47
  • @TylerGubala if you want to create annotated timeline chart the 2nd column needs to have number (numeric) values. array you imported from Patches!B1:B was in text format so timeline chart resulted in error you mentioned. you can test it with =isnumber(Patches!b1) and =isnumber(Sheet20!b1). first is FALSE 2nd is TRUE (because array was re-formated from text into values). so to answer your question: its not byproduct of arrayformula. and it was caused by not converting B column (imported data due array) into values – user0 Jan 16 at 12:48
  • 1
    @TylerGubala I wouldn't say "wrong"... just in this case you forgot the middle step to convert array into array with values – user0 Jan 16 at 13:48
  • 1
    That makes much more sense now. Thank you for the clarification. – user205834 Jan 16 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy