GoogleFinace convert dd/mm/yy to yy,mm,dd format

beetlej

I am trying to use blew expression in Google SpreadSheet.

=min(GoogleFinance("HKDUSD","price",date(A2)-1,date(A2)))

But since A2 data is in mm/dd/year format, we need to convert it to yy,mm,dd format as parameter of date().Does Google SpeadSheet has some API to do such convert?

Aurielle Perlmann

It actually works if you just add the dates as they are without the date function wrapped around them

=min(GoogleFinance("HKDUSD","price",L1-1,L1))

enter image description here

Edited: To answer your follow up question about the date format - a formulaic workaround you do uses regex replace and basically just swaps the two capture groups in month and day with this regex "(\w+)/(\w+)":

=min(GoogleFinance("HKDUSD","price",REGEXREPLACE(L1,"(\w+)/(\w+)","$2/$1")-1,REGEXREPLACE(L1,"(\w+)/(\w+)","$2/$1")))

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Android - change Date Format to dd/mm/yy

puzzled with how to convert ddmmyy string to dd-MMM-yy date

Date format dd/mm/yy

Convert date with format dd.mm.yy to yyyy-mm-dd in python

What is yy-mm-dd'D' format?

Convert dd, mm, yy arrays to numpy datetime

SQL convert date dmmyyyy and ddmmyy to yyyy-mm-dd

How to change/convert input date format from MM dd yy to yyyy-mm-dd on submit

The simplest way to convert a long date format to YY/MM/DD HTML

convert TZ date format to "dd/mm/yy hh:mm:ss" in Javascript

How to convert date dd/mm/YY to mm-dd-YY format in PHP

how to convert sql date formate unix time stamp to date format as dd mm yy format?

want to convert string mm/dd/yy to date yyyy/mm/dd format in Sybase

SAS numeric date format to dd/mm/yy

convert epoch time into dd/mm/yy in a file

How do I convert a DateTime variable to return date in the 'mm/dd/yy' format

Regular expression convert Date format from dd-mon-yy to dd/MM/yy

Convert MySQL date format to DD/MM/YY date format using Javascript

How to convert excel dates dd/mm/yy into number format such as 01011994

How to convert the given date to dd-mm-yy format in javascript?

Convert json string to date in dd-mm-yy format using javascript

How to convert "12/20/2018 1:30 AM" format to the YY-MM-DD hh:mm:ss in angular js?

How to convert date format "dd/mm/yy" to "yy/mm/dd" for mysql database inserting?

Convert UTC format to dd/mm/yy hh:mm:ss format in pandas?

Javascript convert date format from "dd/mm/yy" to "mm/dd/yyyy"

pyspark sql convert date format from mm/dd/yy hh:mm or yyyy-mm-dd hh:mm:ss into yyyy-mm-dd hh:mm format

Convert timestamp to "YY/MM/DD" with JS

How to convert an ISO time format to something like dd/mm/yy, hh:mm:ss AM/PM?

Convert yy.mmd(d) date format to yyyy-mm-dd in R