Query function with manipulation of dates

This post is in continuation of my earlier post on query function. for basic understand the query function have a look at the following link https://anandexcels.wordpress.com/2013/11/01/query-function-in-google-sheets/



Now we will see how to manipulate Query function with DATES, manipulation of date is query function is tricky. some query does not understand the date because in the spreadsheets dates are treated as serial numbers and query understanding it as text. We have to trick the query function

(Q) Suppose we want list the data based on the certain dates referring to cell

we will continue to use the old example in my first query post

solution is as follows:

=QUERY(A:D;”select * where D>=date”””&text(F1,”yyyy-mm-dd”)&””” and D<=date”””&text(G1,”yyyy-mm-dd”)&””””)


where F1 is the start date and G1 is the end date


(1) There are three double quote before text function and after text function

(2) we have used the text function to convert the numbers into text

(3) date has to be formatted in yyyy-mm-dd format only.

the link to spreadsheet is available in my first blogpost listed above


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: