How to substitute the date in the formula?

Question

Google Sheets

There is a link that parses the dollar rate from the Central Bank website:

=IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp?date_req=11/05/2020"; "//ValCurs/Valute[CharCode=""USD""]/Value")

This link indicates the date and the value equal to the dollar rate on the specified day is substituted into the cell.


The question is: is it possible to automate the substitution of the date directly into the formula from the cell, so as not to do it manually? If yes, please tell me how?

Answer 1

The best way would be to use the TEXT formula

=IMPORTXML(
  "http://www.cbr.ru/scripts/XML_daily.asp?date_req=" & TEXT(A2;"DD/MM/YYYY");
  "//ValCurs/Valute[CharCode=""USD""]/Value"
)