How to set summary data in google sheet relative to dates?

Question

Google Sheets

Can you please tell me how to write the formula? Which day I fight.
File with numbers and formulas at the link - https://docs.google.com/spreadsheets/d/1TZeOMivZ5d...

The first two sheets - this is how it was planned to make the file. Now, if the cell with the date (on the first sheet) is empty, then the current dollar rate is taken. If I write a specific date, then the formula does not work. On the third sheet, he showed the same formula, when it turns out the other way around - if I write a specific date, then everything counts correctly, but for an automatic date (= TODAY ()), it gives an error. I can not find where the "plug".

Maybe there is a combination (one formula or several cells with formulas) so that you can see the current cost and cost in the table, taking into account the dollar exchange rate on a specific date.

Answer 1

You are "passing" the date as a number, but you need it as template-formatted text.
It was:

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

It became:

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