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/

https://anandexcels.wordpress.com/2013/11/07/single-column-manipulation-using-query-function/

https://anandexcels.wordpress.com/2013/11/08/query-function-refering-to-cell-value/

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

Observations

(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

 

UNIQUE FUNCTION

UNIQUE

Unique function is unique to google docs spreadsheets, the Returns only the unique rows in the source array, duplicates will be ignored

The syntax of the function is as follows:

UNIQUE(SourceArray)

SourceArray:  range of cells, number, date or string, data has to be of the same type, it can also be array formula results.

Note:

  • The output of the unique function will in the same order as that of SourceArray.

  • unique values will give only for vertical array only

  • to get unique values from a horizontal array we have to use the transpose function inside the unique function.

Example 1:



the data is cell A2:A11 to extract the unique value from the formula used in cell B2 is

=unique(A2:A1)

Example 2:


we can extract the unique values from column A&B the formula in cell C2 is

=ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(A2:B&CHAR(9));CHAR(9)))))

the explanation to the above formula at the following link

https://anandexcels.wordpress.com/2012/02/14/extracting-unique-records-from-two-columns/