CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Draco D
Draco D

Reputation: 334

Using Query to get the dates from a cell Google sheets

Context

Following on from this question, I am trying to make this more dynamic. I want to input data in a cell 'D1' to then make Query bring back the data from another spreadsheet. Below is a depiction of what this looks like.

enter image description here

This is the formula I tried: =QUERY(Query(ImportRange("ID", "AvevaGroupPrice"),"offset 1", 0), "where Col1 >= Date " &D1) I used this format because eventually I want to create a dynamic date range where I type the respective dates I need in the first two 'D' rows.

Problem

How can I make this work using data in cell D1?

Upvotes: 1

Views: 2387

Answers (1)

Kristkun
Kristkun

Reputation: 5953

Based on the Query Language Documentation, when you include date keyword you need to use a date with yyyy-mm-dd format

In your current function, the date was converted to a number when appended to the string.

Sample: ="date "&D1

Result: date 43830

You need to use TEXT() to convert the date to yyyy-mm-dd format.

Sample: ="where Col1 >= Date '"&TEXT(D1,"yyyy-mm-dd")&"'"

Result: where Col1 >= Date '2019-12-31'

(UPDATE)

You can also use TO_TEXT() to convert the Cell Value to a text value.

Sample: ="where Col1 >= Date '"&To_text(D1)&"'"

Result: where Col1 >= Date '2019-12-31'

Your Formula:

=QUERY(Query(ImportRange("ID", "AvevaGroupPrice"),"offset 1", 0), "where Col1 >= Date '"&TEXT(D1,"yyyy-mm-dd")&"'")

or

=QUERY(Query(ImportRange("ID", "AvevaGroupPrice"),"offset 1", 0), "where Col1 >= Date '"&To_text(D1)&"'")

Upvotes: 2

Related Questions