Issue referencing a date when parsing JSON

Ask the Community
#1

Hi,

Not sure if this is a bug per se but I’m trying to parse a value from a list in a JSON but am having difficulty referencing part of my request to a cell containing a date. I’m getting a #N/A error message.

The request that’s returning the error message is the following:
=PARSE($B8,"[‘Technical Analysis: SMA’].[’"&D8&"’].['SMA’]”)
D8 contains the date I’m trying to reference.

If I replace “&D8&” by writing the date directly - i.e. so the request reads:
=PARSE($B8,"[‘Technical Analysis: SMA’].[‘2018-08-02’].[‘SMA’]”)
it works completely fine. It also works fine if I populate D8 by parsing a date within the JSON itself - in this instance, I’m using an Alpha Vantage API so can get today’s date by parsing the ‘Last refreshed’ value (but my issue is that the date I want to reference isn’t today’s date).

Is there an easy fix for this?

Many thanks,

Luca

Building a basic technical trading tool
#2

Hello @lucabertolimitchell

Thanks for writing about this. This is a known issue. We will fix it as soon as we reach it on our priority list. Alternatively, there is a solution that should solve your problem (below).

What’s happening? In any spreadsheet, dates are saved as numbers, not as text. So, =NOW() displays as 2018-08-03 14:33:35 but in fact is saved as 43315.61. We currently don’t have a way of accessing the display format when using the cell in other formulas, which means your parse is trying to access the value ['43314.00'] instead of ['2018-08-02'].

There is a solution though!
Replace

  • &D8&
    With
  • &year(D8)&"-"&if(month(d8)<10,"0"&month(d8),month(d8))&"-"&if(day(d8)<10,"0"&day(d8),day(d8))&

This should, in principle, generate the string you’re looking for.
I hope this helps!

:lollipop: :robot:

#3

Thanks @humberto

That makes sense and the solution has worked so all good now.

:smiley: