Change date format in googlefinance (historical)

Asked Oct 10 '20 at 07:00

Active Oct 10 '20 at 07:00

Viewed 1,709 times

I have a google sheets formula to get an historical rate for a determined currency pair:

GOOGLEFINANCE("CURRENCY:"&A2,"price",DATE(YEAR(B3),MONTH(B3),DAY(B3)),DATE(YEAR(B4),MONTH(B4),DAY(B4)),"DAILY")

It outputs the date as follows :

Date    
12/06/2019 23:58:00
13/06/2019 23:58:00 
14/06/2019 23:58:00 
15/06/2019 23:58:00
16/06/2019 23:58:00

When I need to have the date formatted as 12/06/2019 00:00:00, getting rid of the H:M:s

I tried a query in order to format the date column under the required format:

=query(GOOGLEFINANCE("CURRENCY:"&A2,"price",DATE(YEAR(B3),MONTH(B3),DAY(B3)),DATE(YEAR(B4),MONTH(B4),DAY(B4)),"DAILY"),"select Col1, Col2 FORMAT Col1 'YYYY-MM-DD 00:00:00'")

the column is reformatted but the underlying date stays with the incorrect hour:minutes:seconds number

I could add a third column with an ArrayFormula to get the first column formatted correctly, but I would like to keep the formula in a more convenient 2 columns format.

asked Oct 10 '20 at 07:00

BTC75

2

Why don't you just use `left(yourfinanceformulahere,10)` to get just the date. – Marios Oct 10 '20 at 08:34
How do you use a `left` formula inside `googlefinance`? – BTC75 Oct 10 '20 at 14:59
1

Use it outside. – Marios Oct 10 '20 at 15:01
You can remove the time (decimal part of date) by doing `=INT(XY)`, the format the cell as date – Aldo Canepa Apr 10 '22 at 23:24

Change date format in googlefinance (historical)

0 Answers0